[GENERAL] How to retrieve values of 'array' field using C library (libpq)
Hi. I use libpq in my program. And I can retrieve int and symbolic fields as simple as int unameFN, moneyFN... char *unamePTR, *moneyPTR... unameFN = PQfnumber(res, uname); moneyFN = PQfnumber(res, money); unamePTR = PQgetvalue(res, 0, unameFN); moneyPTR = PQgetvalue(res, 0, moneyFN); themoney = ntohl(*((uint32_t *) moneyPTR)); printf(user-[uname:%s money:%d], unamePTR, themoney); Then I tried to use array type and no success: int arFN... char *arPTR... arN = PQfnumber(res, woo); arPTR = PQgetvalue(res, 0, arFN); printf(user-[woo:%s], arPTR); I can convert it with array_to_string() and got it as some text. My array is the array of integer values. So maybe there is some native method to retrieve them (as in the 'themoney' example)? -- antonvm -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [HACKERS] [GENERAL] Issues with generate_series using integer boundaries
On 9 February 2011 02:11, Robert Haas robertmh...@gmail.com wrote: On Tue, Feb 8, 2011 at 8:30 PM, Andrew Dunstan and...@dunslane.net wrote: Quite right, but the commitfest manager isn't meant to be a substitute for one. Bug fixes aren't subject to the same restrictions of feature changes. Another option would be to add this here: http://wiki.postgresql.org/wiki/PostgreSQL_9.1_Open_Items I've removed it from the commitfest because it really doesn't belong there, and I've added it to the open items list. Thanks -- Thom Brown Twitter: @darkixion IRC (freenode): dark_ixion Registered Linux user: #516935 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Possible Bug
Hi,
I think I found something strange in PostgreSQL behavior. Here is an
example:
testdb=# CREATE TABLE test1 (test2 text, test3 text);
CREATE TABLE
testdb=# SELECT A.name FROM test1 A;
name
--
(0 rows)
testdb=# INSERT INTO test1 (test2,test3) VALUES ('1','2');
INSERT 0 1
testdb=# INSERT INTO test1 (test2,test3) VALUES ('3','4');
INSERT 0 1
testdb=# SELECT A.name FROM test1 A;
name
---
(1,2)
(3,4)
(2 rows)
customer.20080408=# SELECT name FROM test1;
ERROR: column name does not exist
LINE 1: SELECT name FROM test1;
testdb=# SELECT version();
version
--
PostgreSQL 8.4.5 on amd64-portbld-freebsd8.1, compiled by GCC cc (GCC)
4.2.1 20070719 [FreeBSD], 64-bit
(1 row)
Obviously there is no column with name name, but the SELECT query
(ONLY WHEN I USE ALIASES) returns result for it.
And if I have a column with name name_en and by mistake write it
name, instead of error I receive strange data (ARRAY from all
columns), that I don't expect.
Could you tell me if this is a bug or some feature I haven't heard of.
Best regards,
Kaloyan Iliev
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] créer une DLL pour Postgresql 9 avec visual C++ [2008]
Bonjour à tous, dans le cadre d'un projet académique, je dois développer une extension de postgresql 9 sous windows en utilisant visual C++ express 2008 (souci de compatibilité avec une extension préalablement développée dans le même environnement ) pour créer la DLL qui réalise l'extension. Si j'ai compris le principe général pour développer une extension, ma difficulté réside au niveau de la compilation du code sur visual c++. Apparemment il y a des conflits entre le C utilisé pour postgresql et celui de visual C++ alors un certain nombre de configurations et d'adaptations est nécessaire pour compiler des sources postgresql sur Visual C++. Pour commencer, j'ai pris la source complex.c du répertoire /tutorial de postgresql j'ai fait les include des repertoires nécessaires, j'ai ajouté les clauses extern C selon ce qui était spécifié dans certains forums que j'ai visités, mais je n'arrive pas toujours à compiler complex.c correctement. Des erreurs persistent sur certaines fonctions dépréciées deprecated; là aussi j'ai ajouté la clause _CRT_SECURE_NO_DEPRECATE et _CRT_SECURE_NO_WARNINGS sans succès. Alors si quelqu'un aurait une idée, un tutoriel ou tout autre chose qui pourrait m'aider à démarrer, c'est bienvenue. Merci à tous -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to retrieve values of 'array' field using C library (libpq)
Hey Anton, 2011/2/9 Anton Maksimenkov anton...@gmail.com Hi. I use libpq in my program. And I can retrieve int and symbolic fields as simple as int unameFN, moneyFN... char *unamePTR, *moneyPTR... unameFN = PQfnumber(res, uname); moneyFN = PQfnumber(res, money); unamePTR = PQgetvalue(res, 0, unameFN); moneyPTR = PQgetvalue(res, 0, moneyFN); themoney = ntohl(*((uint32_t *) moneyPTR)); printf(user-[uname:%s money:%d], unamePTR, themoney); Then I tried to use array type and no success: int arFN... char *arPTR... arN = PQfnumber(res, woo); arPTR = PQgetvalue(res, 0, arFN); printf(user-[woo:%s], arPTR); I can convert it with array_to_string() and got it as some text. My array is the array of integer values. So maybe there is some native method to retrieve them (as in the 'themoney' example)? Not in libpq. Probably, in libpqtypes. -- antonvm -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- // Dmitriy.
[GENERAL] Re: [pgsql-fr-generale] créer une DLL pour Postgresql 9 avec visual C++ [2008
Bonjour, Je n'ai personnellement pas rencontré de problème avec postgres 8.3 et visual C++ express 2008 dans le cadre du projet JDONREFv2 (forge Addulact - avec les sources et indications pour compilation sous windows si cela vous intéresse). J'ai toutefois du ajouter le windows sdk pour compiler proprement les librairies, et ajuster certains paramètres dans le fichier pg_config.h (qui est fait à cet usage). En espérant que cela vous aura été utile. Salutations, Moquet Julien (tél : 01 55 43 64 55) Architecte DOSTL SDSIC SEPL BIL Préfecture de Police Message original Sujet : [pgsql-fr-generale] créer une DLL pour Postgresql 9 avec visual C++ [2008] De : michel wildcat m.wildca...@gmail.com Pour : pgsql-nov...@postgresql.org, pgsql-general@postgresql.org, pgsql-fr-gener...@postgresql.org, pgsql-fr-nov...@postgresql.org Date : 09/02/2011 12:40 Bonjour à tous, dans le cadre d'un projet académique, je dois développer une extension de postgresql 9 sous windows en utilisant visual C++ express 2008 (souci de compatibilité avec une extension préalablement développée dans le même environnement ) pour créer la DLL qui réalise l'extension. Si j'ai compris le principe général pour développer une extension, ma difficulté réside au niveau de la compilation du code sur visual c++. Apparemment il y a des conflits entre le C utilisé pour postgresql et celui de visual C++ alors un certain nombre de configurations et d'adaptations est nécessaire pour compiler des sources postgresql sur Visual C++. Pour commencer, j'ai pris la source complex.c du répertoire /tutorial de postgresql j'ai fait les include des repertoires nécessaires, j'ai ajouté les clauses extern C selon ce qui était spécifié dans certains forums que j'ai visités, mais je n'arrive pas toujours à compiler complex.c correctement. Des erreurs persistent sur certaines fonctions dépréciées deprecated; là aussi j'ai ajouté la clause _CRT_SECURE_NO_DEPRECATE et _CRT_SECURE_NO_WARNINGS sans succès. Alors si quelqu'un aurait une idée, un tutoriel ou tout autre chose qui pourrait m'aider à démarrer, c'est bienvenue. Merci à tous -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] atttypmod encoding
Catalog pg_attribute contains interesting type-specific information in attribute atttypmod. Can someone verify that these encodings are correct? CHAR and VARCHAR: 4 + the declared length. Example: CHAR(5) - 9 in atttypmod NUMERIC(p,s): 4 + the long with p in the left-most 4 bytes and s in the right-most
Re: [GENERAL] PostgreSQL 9.0 Streaming Replication Configuration
On Wed, Feb 09, 2011 at 01:14:05AM -0600, Ogden wrote: Thank you for letting me know about pg_controldata. I have been playing around with this tool. really interesting event/failure last night for me. I started a new thread on the failure in the admin list. my streaming rep without wal archiving in place seems to be corrupted. I think you will be interested in it. I could have tacked it on here, but I thought it needed to stand out. Regards, Ray -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Possible Bug
On 2011-02-09 10.51, Kaloyan Iliev Iliev wrote:
Hi,
I think I found something strange in PostgreSQL behavior. Here is an
example:
testdb=# CREATE TABLE test1 (test2 text, test3 text);
CREATE TABLE
testdb=# SELECT A.name FROM test1 A;
name
--
(0 rows)
testdb=# INSERT INTO test1 (test2,test3) VALUES ('1','2');
INSERT 0 1
testdb=# INSERT INTO test1 (test2,test3) VALUES ('3','4');
INSERT 0 1
testdb=# SELECT A.name FROM test1 A;
name ---
(1,2)
(3,4)
(2 rows)
customer.20080408=# SELECT name FROM test1;
ERROR: column name does not exist
LINE 1: SELECT name FROM test1;
testdb=# SELECT version();
version
--
PostgreSQL 8.4.5 on amd64-portbld-freebsd8.1, compiled by GCC cc (GCC)
4.2.1 20070719 [FreeBSD], 64-bit
(1 row)
Obviously there is no column with name name, but the SELECT query
(ONLY WHEN I USE ALIASES) returns result for it.
And if I have a column with name name_en and by mistake write it
name, instead of error I receive strange data (ARRAY from all
columns), that I don't expect.
Could you tell me if this is a bug or some feature I haven't heard of.
Look up functional notation in
http://www.postgresql.org/docs/9.0/static/xfunc-sql.html#AEN46042
These are the same thing:
select name(test1) from test1;
select test.name from test1;
Best regards,
Kaloyan Iliev
--
Regards,
Robert roppert Gravsjö
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] tuning postgresql writes to disk
On Mon, Feb 7, 2011 at 7:43 PM, Vinubalaji Gopal vinubal...@gmail.comwrote: Thank you. I will try to run some performance tests using the async commit option. Is there an easy way to find the lost transactions or does it have to be handled by the application? By definition, your application has to be able to detect it. If the DB were able find them, they wouldn't be lost. Personally, I turn off synchronous commit on a per-connection basis based on what my application is doing. When I insert or update data, I need to ensure it is there; when I move data around or do batch processing on it, it is usually a restartable operation.
Re: [GENERAL] Possible Bug
On Wednesday, February 09, 2011 1:51:38 am Kaloyan Iliev Iliev wrote:
Hi,
I think I found something strange in PostgreSQL behavior. Here is an
example:
testdb=# CREATE TABLE test1 (test2 text, test3 text);
CREATE TABLE
testdb=# SELECT A.name FROM test1 A;
name
--
(0 rows)
testdb=# INSERT INTO test1 (test2,test3) VALUES ('1','2');
INSERT 0 1
testdb=# INSERT INTO test1 (test2,test3) VALUES ('3','4');
INSERT 0 1
testdb=# SELECT A.name FROM test1 A;
name
---
(1,2)
(3,4)
(2 rows)
customer.20080408=# SELECT name FROM test1;
ERROR: column name does not exist
LINE 1: SELECT name FROM test1;
testdb=# SELECT version();
version
---
--- PostgreSQL 8.4.5 on
amd64-portbld-freebsd8.1, compiled by GCC cc (GCC) 4.2.1 20070719
[FreeBSD], 64-bit
(1 row)
Obviously there is no column with name name, but the SELECT query
(ONLY WHEN I USE ALIASES) returns result for it.
And if I have a column with name name_en and by mistake write it
name, instead of error I receive strange data (ARRAY from all
columns), that I don't expect.
Could you tell me if this is a bug or some feature I haven't heard of.
Depending on your point of view both. For a good explanation see:
http://www.depesz.com/index.php/2010/11/08/waiting-for-9-1-removed-autocast-
footgun/
As detailed above this feature will go away in 9.1.
Best regards,
Kaloyan Iliev
--
Adrian Klaver
adrian.kla...@gmail.com
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Why I need to use SETOF TEXT when I'm returning single column?
If I'll make my functions like this: create function test( out a text, out b text) returns setof record ... it works well. but if i have just one column in returned recordset, I cannot: create function test( out a text ) returns setof record ... shows: ERROR: function result type must be text because of OUT parameters it's bad because I can't have the column names the way I want it to be named! (well, I could, using custom datatype, but it's besides the point. is there any particular reason why returning single-column setof record is not allowed? Best regards, depesz -- Linkedin: http://www.linkedin.com/in/depesz / blog: http://www.depesz.com/ jid/gtalk: dep...@depesz.com / aim:depeszhdl / skype:depesz_hdl / gg:6749007 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pg_restore validation?
On Tue, Feb 8, 2011 at 8:06 PM, u235sentinel u235senti...@gmail.com wrote: Is there a way we can validate a postgers backup? (short of restoring it somewhere) Define validate for your purpose. Once you do that, then you can come up with the procedure for accomplishing your validation. Hint: simply restoring it somewhere may not be sufficient...
Re: [GENERAL] pg_restore validation?
On 02/09/2011 07:52 AM, Vick Khera wrote: On Tue, Feb 8, 2011 at 8:06 PM, u235sentinel u235senti...@gmail.com mailto:u235senti...@gmail.com wrote: Is there a way we can validate a postgers backup? (short of restoring it somewhere) Define validate for your purpose. Once you do that, then you can come up with the procedure for accomplishing your validation. Hint: simply restoring it somewhere may not be sufficient... For validate what I'm looking to do is provide either some log or message provided by postgres that will alert us when/if the backup did 'not' complete successfully. So I guess it's more of a pg_dump validation I'm looking into. I've been googling and found pg_rman which doesn't sound like what I'm looking for. It has a validate function but their documentation is a little light. Thanks!
Re: [GENERAL] pg_restore validation?
On 09/02/2011 15:10, u235sentinel wrote: On 02/09/2011 07:52 AM, Vick Khera wrote: On Tue, Feb 8, 2011 at 8:06 PM, u235sentinel u235senti...@gmail.com mailto:u235senti...@gmail.com wrote: Is there a way we can validate a postgers backup? (short of restoring it somewhere) Define validate for your purpose. Once you do that, then you can come up with the procedure for accomplishing your validation. Hint: simply restoring it somewhere may not be sufficient... For validate what I'm looking to do is provide either some log or message provided by postgres that will alert us when/if the backup did 'not' complete successfully. So I guess it's more of a pg_dump validation I'm looking into. Well, pg_dump returns 0 on success, and error messages on standard error on failure. Ray. -- Raymond O'Donnell :: Galway :: Ireland r...@iol.ie -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] plperl.dll on windows with postgresql 9.0.3
On 2/8/2011 12:03 PM, Robert Fitzpatrick wrote: On 2/8/2011 11:58 AM, Robert Fitzpatrick wrote: could not load library “C:/Program Files/PostgreSQL/8.3/lib/plperl.dll”: The specified module could not be found. SQL state: 58P01 Sorry the correct error I am getting is... could not load library “C:/Program Files/PostgreSQL/9.0/lib/plperl.dll”: The specified module could not be found. SQL state: 58P01 The previous was copied from a post found on the net when searching for solutions. Finally found the solution to this issue, it seems PostgreSQL is looking for perl510.dll, or earlier perhaps... http://stackoverflow.com/questions/4129479/strawberry-perl-5-12-as-postgresql-9-0s-plperl-on-win32 I also renamed my ActivePerl library from perl512.dll and perl510.dll to solve. Does this mean I may run into issues with the new Perl? I also tried 5.8.x and could not get plperl.dll installed with same error. -- Robert rob...@webtent.org -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pg_dump: schema with OID 58698 does not exist
On Tue, Feb 08, 2011 at 10:16:02PM -0500, Tom Lane wrote: - David Kerr d...@mr-paradox.net writes: - I'm getting the above error in one of my dev DBs. - - Would you poke around in the system catalogs and find where the dangling - reference is located? Have you got any idea of how to reproduce this - failure from a standing start? - - regards, tom lane - Ok i'll dig around. I spoke with the developer, he claims not to have done anything to the DB. so i don't know how to reproduce the state. (but i do get the error every time i use pg_dump on the DB). it seems that this DB is mainly used as a target for junit testing. Also, i don't know if this is related but I'm seeing quite a few: Feb 9 17:04:17 db001 postgres-9[21285]: [28-1] user=,db=,pid=21285,2011-02-09 17:04:17 UTC WARNING: PD_ALL_VISIBLE flag was incorrectly set in relation test_event_result_fact page 4223 Feb 9 17:04:17 db001 postgres-9[21285]: [29-1] user=,db=,pid=21285,2011-02-09 17:04:17 UTC WARNING: PD_ALL_VISIBLE flag was incorrectly set in relation test_event_result_fact page 4224 Feb 9 17:04:17 db001 postgres-9[21285]: [30-1] user=,db=,pid=21285,2011-02-09 17:04:17 UTC WARNING: PD_ALL_VISIBLE flag was incorrectly set in relation test_event_result_fact page 4225 Feb 9 17:04:17 db001 postgres-9[21285]: [31-1] user=,db=,pid=21285,2011-02-09 17:04:17 UTC WARNING: PD_ALL_VISIBLE flag was incorrectly set in relation test_event_result_fact page 4226 Feb 9 17:04:17 db001 postgres-9[21285]: [32-1] user=,db=,pid=21285,2011-02-09 17:04:17 UTC WARNING: PD_ALL_VISIBLE flag was incorrectly set in relation test_event_result_fact page 4227 Feb 9 17:04:17 db001 postgres-9[21285]: [33-1] user=,db=,pid=21285,2011-02-09 17:04:17 UTC WARNING: PD_ALL_VISIBLE flag was incorrectly set in relation test_event_result_fact page 4228 Feb 9 17:04:17 db001 postgres-9[21285]: [34-1] user=,db=,pid=21285,2011-02-09 17:04:17 UTC WARNING: PD_ALL_VISIBLE flag was incorrectly set in relation test_event_result_fact page 4229 It seems like a system process is reporting it, i tried to vacuum all of my DBs to try to narrow down which DB it's in - didn't generate the error. I suppose it's possible that the process is fixing the data and then it gets broken again. Any pointers would be helpful! Thanks Dave -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pg_dump: schema with OID 58698 does not exist
On Wed, Feb 09, 2011 at 09:42:36AM -0800, David Kerr wrote: - On Tue, Feb 08, 2011 at 10:16:02PM -0500, Tom Lane wrote: - - David Kerr d...@mr-paradox.net writes: - - I'm getting the above error in one of my dev DBs. - - - - Would you poke around in the system catalogs and find where the dangling - - reference is located? Have you got any idea of how to reproduce this - - failure from a standing start? - - - - regards, tom lane - - - - Ok i'll dig around. - - I spoke with the developer, he claims not to have done anything to the DB. so i don't - know how to reproduce the state. (but i do get the error every time i use pg_dump on - the DB). it seems that this DB is mainly used as a target for junit testing. - - Also, i don't know if this is related but I'm seeing quite a few: - Feb 9 17:04:17 db001 postgres-9[21285]: [28-1] user=,db=,pid=21285,2011-02-09 17:04:17 UTC WARNING: PD_ALL_VISIBLE flag was incorrectly set in relation test_event_result_fact page 4223 - Feb 9 17:04:17 db001 postgres-9[21285]: [29-1] user=,db=,pid=21285,2011-02-09 17:04:17 UTC WARNING: PD_ALL_VISIBLE flag was incorrectly set in relation test_event_result_fact page 4224 - Feb 9 17:04:17 db001 postgres-9[21285]: [30-1] user=,db=,pid=21285,2011-02-09 17:04:17 UTC WARNING: PD_ALL_VISIBLE flag was incorrectly set in relation test_event_result_fact page 4225 - Feb 9 17:04:17 db001 postgres-9[21285]: [31-1] user=,db=,pid=21285,2011-02-09 17:04:17 UTC WARNING: PD_ALL_VISIBLE flag was incorrectly set in relation test_event_result_fact page 4226 - Feb 9 17:04:17 db001 postgres-9[21285]: [32-1] user=,db=,pid=21285,2011-02-09 17:04:17 UTC WARNING: PD_ALL_VISIBLE flag was incorrectly set in relation test_event_result_fact page 4227 - Feb 9 17:04:17 db001 postgres-9[21285]: [33-1] user=,db=,pid=21285,2011-02-09 17:04:17 UTC WARNING: PD_ALL_VISIBLE flag was incorrectly set in relation test_event_result_fact page 4228 - Feb 9 17:04:17 db001 postgres-9[21285]: [34-1] user=,db=,pid=21285,2011-02-09 17:04:17 UTC WARNING: PD_ALL_VISIBLE flag was incorrectly set in relation test_event_result_fact page 4229 - - - It seems like a system process is reporting it, i tried to vacuum all of my DBs to try to - narrow down which DB it's in - didn't generate the error. - - I suppose it's possible that the process is fixing the data and then it gets broken again. - - Any pointers would be helpful! Ok, I found the bad entries, 2 tables a sequence and 2 primary key indexes are associated with the wrong (invalid / nonexistant ) schema. However, there are correct entries for those objects as well. So these are definitly just dangling references. When we do a data refresh here, i do a drop schema cascade; in the DB and then pg_restore -Fc --disable-triggers the datasets are made by pg_dump -Fc --disable-triggers -s -n schema The only thing strange that may come up is that the dumps were created by 8.3. (also, i've got multiple developers all with their own DB and this is the only one with this particular problem) The 'PD_ALL_VISIBLE' error above seems to not be related to this particular problem. (although it could still be in this database). Dave -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Will rename database get replicated?
Hi: Will V9.0.1 streaming replication replicate the effects of alter database foo rename to fee ? Will it replicate analyze (I do this after a big DB load to give the query optimizer good stats to work with)? If I kill a user on the master (using pg_ctl kill ABRT procpid), will that goof up replication if that user was in the middle of a write transation? I've read in http://wiki.postgresql.org/wiki/Binary_Replication_Tutorial#5_Minutes_to_Simple_Replication ... What Can't You Do With Binary Replication? [PicExportError] Replicate a specific table, schema, or database. Binary replication is the entire Postgres instance (or cluster). [PicExportError] Multi-master replication. Multi-master binary replication is probably technically impossible. [PicExportError] Replicate between different versions of PostgreSQL, or between different platforms. [PicExportError] Set up replication without administration rights on the server. Sorry, working on it. [PicExportError] Replicate data synchronously, guaranteeing zero data loss. But ... this is coming in PostgreSQL 9.1. Beside the last one, are any of the other 4 going to be addressed in 9.1 ? Thanks in advance.
Re: [GENERAL] pg_dump: schema with OID 58698 does not exist
David Kerr d...@mr-paradox.net writes: Ok, I found the bad entries, 2 tables a sequence and 2 primary key indexes are associated with the wrong (invalid / nonexistant ) schema. However, there are correct entries for those objects as well. So these are definitly just dangling references. Please be more specific. What are the bad entries exactly (what values, in which columns of what catalogs) and what do you mean by there are correct entries as well? When we do a data refresh here, i do a drop schema cascade; in the DB and then pg_restore -Fc --disable-triggers Hm. We have seen occasional reports of drop cascade failing to delete all the dependent objects, but it's pretty hard to see how that could happen ... regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Relative ordering in array aggregation
I'm using 8.3.3. I came across the array_accum aggregate function in
the docs:
http://www.postgresql.org/docs/8.3/interactive/xaggr.html
CREATE AGGREGATE array_accum (anyelement)
(
sfunc = array_append,
stype = anyarray,
initcond = '{}'
);
This would be very useful to me, but I have a question about ordering
of values. If I use more than one array_accum in the same select
clause, is there any reliable correspondence between the order of
elements in the returned arrays? In other words, if a have a table
foo like so:
abc
++
aaa | 12 | a1
aaa | 13 | a2
and I run
select a, array_accum (b), array_accum (c) from foo group by a;
then can I expect that the results will always be one of
aaa | {12, 13} | {a1, a2}
or
aaa | {13, 12} | {a2, a1}?
OR, could the query also return the arrays as
aaa | {13, 12} | {a1, a2}
or
aaa | {12, 13} | {a2, a1}?
If so, would there be any way to enforce the first behaviour?
Thanks,
nishad
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pg_dump: schema with OID 58698 does not exist
On Wed, Feb 09, 2011 at 02:15:06PM -0500, Tom Lane wrote: - David Kerr d...@mr-paradox.net writes: - Ok, I found the bad entries, 2 tables a sequence and 2 primary key indexes are associated - with the wrong (invalid / nonexistant ) schema. - - However, there are correct entries for those objects as well. So these are definitly just dangling references. - - Please be more specific. What are the bad entries exactly (what values, - in which columns of what catalogs) and what do you mean by there are - correct entries as well? pg_class has 5 relname entries associated with a relnamespace (58698) that doesn't exist in pg_namespace. Those relname entries corrispond to 2 tables and their PKs and a sequence (for the PK of one of the tables). Those objects do have valid entries in pg_class as well (they point to a different relnamespace) Is that more clear? - When we do a data refresh here, i do a drop schema cascade; in the DB and then - pg_restore -Fc --disable-triggers - - Hm. We have seen occasional reports of drop cascade failing to delete - all the dependent objects, but it's pretty hard to see how that could - happen ... I agree! Dave -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] problematic view definition
Hi all !
Attached find some table and view definitions from the
GNUmed (www.gnumed.de) database.
Unfortunately I do not understand why PostgreSQL says
psql:xx.sql:14: ERROR: could not implement UNION
DETAIL: Some of the datatypes only support hashing, while others only
support sorting.
when I say
select * from dem.v_message_inbox;
I mean, I (hope I) do understand what PostgreSQL tries to
tell me but I don't know how to find out which columns are
affected ...
Thanks !
Karsten
--
GPG key ID E4071346 @ gpg-keyserver.de
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
Output format is wrapped.
Expanded display is on.
Table dem.message_inbox
Column | Type |
Modifiers
+--+---
pk_audit | integer | not null default
nextval('audit.audit_fields_pk_audit_seq'::regclass)
row_version| integer | not null default 0
modified_when | timestamp with time zone | not null default now()
modified_by| name | not null default
current_user()
pk | integer | not null default
nextval('dem.provider_inbox_pk_seq'::regclass)
fk_staff | integer |
fk_inbox_item_type | integer | not null
comment| text |
data | text |
importance | smallint | default 0
fk_patient | integer |
ufk_context| integer[]|
Indexes:
provider_inbox_pkey PRIMARY KEY, btree (pk)
Check constraints:
message_must_have_recipient CHECK ((fk_staff IS NULL AND fk_patient IS
NULL) IS FALSE)
provider_inbox_comment_check CHECK (btrim(COALESCE(comment,
'xxxDEFAULTxxx'::text)) ''::text)
provider_inbox_importance_check CHECK (importance = (-1) OR importance =
0 OR importance = 1)
Foreign-key constraints:
message_inbox_fk_patient_fkey FOREIGN KEY (fk_patient) REFERENCES
dem.identity(pk) ON UPDATE CASCADE ON DELETE RESTRICT
provider_inbox_fk_inbox_item_type_fkey FOREIGN KEY (fk_inbox_item_type)
REFERENCES dem.inbox_item_type(pk)
provider_inbox_fk_staff_fkey FOREIGN KEY (fk_staff) REFERENCES
dem.staff(pk)
Triggers:
tr_message_inbox_generic_mod AFTER INSERT OR DELETE OR UPDATE ON
dem.message_inbox DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE
dem.trf_announce_message_inbox_generic_mod_no_pk()
tr_message_inbox_mod AFTER INSERT OR DELETE OR UPDATE ON dem.message_inbox
DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE
dem.trf_announce_message_inbox_mod()
zt_del_message_inbox BEFORE DELETE ON dem.message_inbox FOR EACH ROW
EXECUTE PROCEDURE audit.ft_del_message_inbox()
zt_ins_message_inbox BEFORE INSERT ON dem.message_inbox FOR EACH ROW
EXECUTE PROCEDURE audit.ft_ins_message_inbox()
zt_upd_message_inbox BEFORE UPDATE ON dem.message_inbox FOR EACH ROW
EXECUTE PROCEDURE audit.ft_upd_message_inbox()
Inherits: audit.audit_fields
View dem.v_message_inbox
Column | Type | Modifiers
+--+---
received_when | timestamp with time zone |
provider | text |
importance | integer |
category | text |
l10n_category | text |
type | text |
l10n_type | text |
comment| text |
pk_context | integer[]|
data | text |
pk_inbox_message | integer |
pk_staff | integer |
pk_category| integer |
pk_type| integer |
pk_patient | integer |
is_virtual | boolean |
xmin_message_inbox | xid |
View definition:
(( SELECT mi.modified_when AS received_when, ( SELECT
staff.short_alias
FROM dem.staff
WHERE staff.pk = mi.fk_staff) AS provider,
mi.importance, vit.category, vit.l10n_category, vit.type, vit.l10n_type,
mi.comment, mi.ufk_context AS pk_context, mi.data, mi.pk AS pk_inbox_message,
mi.fk_staff AS pk_staff, vit.pk_category, mi.fk_inbox_item_type AS pk_type,
mi.fk_patient AS pk_patient, false AS is_virtual, mi.xmin AS xmin_message_inbox
FROM dem.message_inbox mi, dem.v_inbox_item_type vit
WHERE mi.fk_inbox_item_type =
Re: [GENERAL] XML Encoding problem
On mån, 2011-02-07 at 12:44 +0100, rsmogura wrote: I have test database with UTF-8 encoding. I putted there XML aЁĄ¡/a, (U+0401, U+0104, U+00A1). I changed client encoding to iso8859-2, as the result of select I got ERROR: character 0xd081 of encoding UTF8 has no equivalent in LATIN2 Stan SQL:22P05. I should got result with characters entities for unparsable characters #...;. Hehe, interesting idea, but it's not implemented that way. We don't alter the XML data, except for the XML declaration. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] problematic view definition
I should have mentioned this is on
PostgreSQL 8.4.5 on i486-pc-linux-gnu, compiled by GCC gcc-4.4.real
(Debian 4.4.5-4) 4.4.5, 32-bit
Karsten
On Wed, Feb 09, 2011 at 11:12:01PM +0100, Karsten Hilbert wrote:
Date: Wed, 9 Feb 2011 23:12:01 +0100
From: Karsten Hilbert karsten.hilb...@gmx.net
To: pgsql-general pgsql-general@postgresql.org
Subject: [GENERAL] problematic view definition
User-Agent: Mutt/1.5.20 (2009-06-14)
Hi all !
Attached find some table and view definitions from the
GNUmed (www.gnumed.de) database.
Unfortunately I do not understand why PostgreSQL says
psql:xx.sql:14: ERROR: could not implement UNION
DETAIL: Some of the datatypes only support hashing, while others only
support sorting.
when I say
select * from dem.v_message_inbox;
I mean, I (hope I) do understand what PostgreSQL tries to
tell me but I don't know how to find out which columns are
affected ...
Thanks !
Karsten
--
GPG key ID E4071346 @ gpg-keyserver.de
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
Output format is wrapped.
Expanded display is on.
Table dem.message_inbox
Column | Type |
Modifiers
+--+---
pk_audit | integer | not null default
nextval('audit.audit_fields_pk_audit_seq'::regclass)
row_version| integer | not null default 0
modified_when | timestamp with time zone | not null default now()
modified_by| name | not null default
current_user()
pk | integer | not null default
nextval('dem.provider_inbox_pk_seq'::regclass)
fk_staff | integer |
fk_inbox_item_type | integer | not null
comment| text |
data | text |
importance | smallint | default 0
fk_patient | integer |
ufk_context| integer[]|
Indexes:
provider_inbox_pkey PRIMARY KEY, btree (pk)
Check constraints:
message_must_have_recipient CHECK ((fk_staff IS NULL AND fk_patient IS
NULL) IS FALSE)
provider_inbox_comment_check CHECK (btrim(COALESCE(comment,
'xxxDEFAULTxxx'::text)) ''::text)
provider_inbox_importance_check CHECK (importance = (-1) OR importance
= 0 OR importance = 1)
Foreign-key constraints:
message_inbox_fk_patient_fkey FOREIGN KEY (fk_patient) REFERENCES
dem.identity(pk) ON UPDATE CASCADE ON DELETE RESTRICT
provider_inbox_fk_inbox_item_type_fkey FOREIGN KEY (fk_inbox_item_type)
REFERENCES dem.inbox_item_type(pk)
provider_inbox_fk_staff_fkey FOREIGN KEY (fk_staff) REFERENCES
dem.staff(pk)
Triggers:
tr_message_inbox_generic_mod AFTER INSERT OR DELETE OR UPDATE ON
dem.message_inbox DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE
PROCEDURE dem.trf_announce_message_inbox_generic_mod_no_pk()
tr_message_inbox_mod AFTER INSERT OR DELETE OR UPDATE ON
dem.message_inbox DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE
PROCEDURE dem.trf_announce_message_inbox_mod()
zt_del_message_inbox BEFORE DELETE ON dem.message_inbox FOR EACH ROW
EXECUTE PROCEDURE audit.ft_del_message_inbox()
zt_ins_message_inbox BEFORE INSERT ON dem.message_inbox FOR EACH ROW
EXECUTE PROCEDURE audit.ft_ins_message_inbox()
zt_upd_message_inbox BEFORE UPDATE ON dem.message_inbox FOR EACH ROW
EXECUTE PROCEDURE audit.ft_upd_message_inbox()
Inherits: audit.audit_fields
View dem.v_message_inbox
Column | Type | Modifiers
+--+---
received_when | timestamp with time zone |
provider | text |
importance | integer |
category | text |
l10n_category | text |
type | text |
l10n_type | text |
comment| text |
pk_context | integer[]|
data | text |
pk_inbox_message | integer |
pk_staff | integer |
pk_category| integer |
pk_type| integer |
pk_patient | integer |
is_virtual | boolean |
xmin_message_inbox | xid |
View definition:
(( SELECT mi.modified_when AS received_when, ( SELECT
staff.short_alias
FROM
Re: [GENERAL] about PostgreSQL 9.0.3 RPMs
On Tue, 2011-02-08 at 18:45 +0900, OTSUKA Kenji wrote: I searched PostgreSQL 9.0.3 RPMs for RHEL5, but couldn't find them. I need to them in my business. They are not yet on the following page. http://yum.pgrpms.org/9.0/redhat/rhel-5Server-x86_64/ When is they available? I uploaded them about 8 hours before. Regards, -- Devrim GÜNDÜZ EnterpriseDB: http://www.enterprisedb.com PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer Community: devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr http://www.gunduz.org Twitter: http://twitter.com/devrimgunduz signature.asc Description: This is a digitally signed message part
Re: [GENERAL] Will rename database get replicated?
Le 09/02/2011 20:06, Gauthier, Dave a écrit : Hi: Will V9.0.1 streaming replication replicate the effects of alter database foo rename to fee ? Yes. Will it replicate analyze (I do this after a big DB load to give the query optimizer good stats to work with)? AFAIK, yes. If I kill a user on the master (using pg_ctl kill ABRT procpid), will that goof up replication if that user was in the middle of a write transation? No. I've read in http://wiki.postgresql.org/wiki/Binary_Replication_Tutorial#5_Minutes_to_Simple_Replication ... What Can't You Do With Binary Replication? [PicExportError] Replicate a specific table, schema, or database. Binary replication is the entire Postgres instance (or cluster). [PicExportError] Multi-master replication. Multi-master binary replication is probably technically impossible. [PicExportError] Replicate between different versions of PostgreSQL, or between different platforms. [PicExportError] Set up replication without administration rights on the server. Sorry, working on it. [PicExportError] Replicate data synchronously, guaranteeing zero data loss. But ... this is coming in PostgreSQL 9.1. Beside the last one, are any of the other 4 going to be addressed in 9.1 ? I know no-one working on the four first. And there is work ongoing for the last one, but it may not make it for 9.1. -- Guillaume http://www.postgresql.fr http://dalibo.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Compellent SAN, anyone?
I'm hunting opinions on Compellent (http://www.compellent.com) storage devices and postgres. Comments, anyone? Experiences? Case studies? Success or horror stories? Thanks in advance. -- Joshua Tolley / eggyknap End Point Corporation http://www.endpoint.com signature.asc Description: Digital signature
[GENERAL] Storing Media Types
I've created a basic table called 'employees' I've been asked to store a profile photo of all employees. I've looked on Google and the 9.0 documentation but can't find any clear instructions on how I would be to insert photo's stored in a local directory on the server (/var/lib/postgres/data/media/pics). I'm trying to understand how I would create an entry into the table I show below to be able to add photo's for each user. Does anyone have an example of what the code would look like and or offer any assistance? ghost= CREATE TABLE employees ( id INT PRIMARY KEY NOT NULL UNIQUE, fname VARCHAR(50) NOT NULL, lname VARCHAR(50) NOT NULL, email VARCHAR(50) UNIQUE NOT NULL, branch VARCHAR(50) NOT NULL, position VARCHAR(50) NOT NULL, office INT NOT NULL, dob DATE NOT NULL, photo ? ) ; NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index employees_pkey for table employees NOTICE: CREATE TABLE / UNIQUE will create implicit index employees_email_key for table employees CREATE TABLE -Carlos -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Relative ordering in array aggregation
praka...@uci.edu writes: I'm using 8.3.3. I came across the array_accum aggregate function in the docs: http://www.postgresql.org/docs/8.3/interactive/xaggr.html This would be very useful to me, but I have a question about ordering of values. If I use more than one array_accum in the same select clause, is there any reliable correspondence between the order of elements in the returned arrays? As long as you don't use DISTINCT (or, in more recent versions than 8.3, within-aggregate ordering of the input rows), all aggregates in the same SELECT list should receive the input rows in the same order. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to retrieve values of 'array' field using C library (libpq)
On Wed, Feb 09, 2011 at 02:53:37PM +0300, Dmitriy Igrishin wrote: My array is the array of integer values. So maybe there is some native method to retrieve them (as in the 'themoney' example)? Not in libpq. Probably, in libpqtypes. I ended up writing one for pgsnmpd, which you're welcome to use. I can't guarantee that writing my own was the best way to do it, or that it's free of bugs. See pg_array.c and pg_array.h at http://git.postgresql.org/gitweb?p=pgsnmpd.git;a=summary -- Joshua Tolley / eggyknap End Point Corporation http://www.endpoint.com signature.asc Description: Digital signature
Re: [GENERAL] Storing Media Types
Photos/Images are binary data and thus should be placed into a bytea typed field. As to HOW you would identify and load the binary data that would be depending upon your programming language and user interface. If you are using a traditional programming language you would simply create a parameterized INSERT statement and then specify that the contents of the binary file would be loaded into that particular parameter. You do NOT explicitly put the binary data into the PostgreSQL's data directory (which you appear to be implying). IF you want to use psql/pgAdmin running on the server to load in a local file you should specify that and wait (or look) for a proper response as I do not know how or if that can be done. David J -Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Carlos Mennens Sent: Wednesday, February 09, 2011 7:30 PM To: pgsql-general@postgresql.org Subject: [GENERAL] Storing Media Types I've created a basic table called 'employees' I've been asked to store a profile photo of all employees. I've looked on Google and the 9.0 documentation but can't find any clear instructions on how I would be to insert photo's stored in a local directory on the server (/var/lib/postgres/data/media/pics). I'm trying to understand how I would create an entry into the table I show below to be able to add photo's for each user. Does anyone have an example of what the code would look like and or offer any assistance? ghost= CREATE TABLE employees ( id INT PRIMARY KEY NOT NULL UNIQUE, fname VARCHAR(50) NOT NULL, lname VARCHAR(50) NOT NULL, email VARCHAR(50) UNIQUE NOT NULL, branch VARCHAR(50) NOT NULL, position VARCHAR(50) NOT NULL, office INT NOT NULL, dob DATE NOT NULL, photo ? ) ; NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index employees_pkey for table employees NOTICE: CREATE TABLE / UNIQUE will create implicit index employees_email_key for table employees CREATE TABLE -Carlos -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] 9.0.X FOR UPDATE|SHARE on Sub-Query Causes cannot extract system attribute from virtual tuple if Sub-Query Returns Records (BUG)
David Johnston pol...@yahoo.com writes: More simply if you run any query of the form: SELECT subquerycolumn FROM ( SELECT subquerycolumn FROM table WHERE [condition] FOR UPDATE -- WHERE is optional but obviously useful; FOR SHARE also causes this behavior ) intermediate The error cannot extract system attribute from virtual tuple is thrown IIF the sub-query returns one or more records. Fixed, thanks for the report! http://git.postgresql.org/gitweb?p=postgresql.git;a=commitdiff;h=d5478c3391f8f1a243abbc3d9253aac3d6d3538e regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Full text t_tsquery is giving error
I am getting the following results:
select * from ts_debug('Rumman (AI)');
alias | description | token |dictionaries| dictionary |
lexemes
---+-+++--+--
asciiword | Word, all ASCII | Rumman | {syn,english_stem} | english_stem |
{rumman}
blank | Space symbols | ( | {} | |
asciiword | Word, all ASCII | AI | {syn,english_stem} | english_stem |
{ai}
blank | Space symbols | ) | {} | |
(4 rows)
But,
select to_tsquery('Rumman (AI)');
ERROR: syntax error in tsquery: Rumman (AI)
I don't know why this is happening.
Any idea please.
Re: [GENERAL] XML Encoding problem
I may write some patch, actually text mode will not be affected, becuase it's text mode, and patch will fail if client encoding is reacher then server (one possiblity in this situation is to XML-encode to client encoding, text- rencode to server encoding) But looking at code same thing could occur with binary recv. I saw there text based XML conversion (it's altering XML in some way). According to doc I can store XML in any encodign using binary mode. I think if text conversion fails, then XML rewrite should occur, and all unparsable character should be converted to XML entities... Actually it's XML, not varchar with parsing :) Peter Eisentraut pete...@gmx.net Wednesday 09 February 2011 23:29:29 On mån, 2011-02-07 at 12:44 +0100, rsmogura wrote: I have test database with UTF-8 encoding. I putted there XML aЁĄ¡/a, (U+0401, U+0104, U+00A1). I changed client encoding to iso8859-2, as the result of select I got ERROR: character 0xd081 of encoding UTF8 has no equivalent in LATIN2 Stan SQL:22P05. I should got result with characters entities for unparsable characters #...;. Hehe, interesting idea, but it's not implemented that way. We don't alter the XML data, except for the XML declaration. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
