Re: [GENERAL] Variadic polymorpic functions
2010/1/25 Vincenzo Romano : > 2010/1/25 Pavel Stehule : >> 2010/1/25 Vincenzo Romano : >>> 2010/1/23 Pavel Stehule : 2010/1/22 Vincenzo Romano : > 2010/1/22 Tom Lane : >> Vincenzo Romano writes: >>> 2010/1/22 Tom Lane : regression=# CREATE FUNCTION q( fmt text, variadic args "any" ) >> >>> And this would allow for a stdarg-like argument list? >> >> Yeah, it should work, given suitable C code. > > Great! > I wrote this function year ago. look on content http://pgfoundry.org/projects/pstcollection/ >>> >>> Pavel, >>> that format() function should be included into official contribs. >>> What about HOWTO compile? >> >> There are not consensus about final semantic - some people prefer >> sprintf like, some others PostgreSQL RAISE NOTICE like. so I'll keep >> it outside. I looking on source of pstcollection - missing >> documentation, missing regress test. I never rebuild it outside >> PostgreSQL source tree - so it could be problem. Now: copy src to >> contrib directory, make, make install - like standard contrib module. >> >> If you would to add some doc or notes, please, add it. > > I figured that out (from the Makefile) > USE_PGXS=1 make install ok, if you would, add account on pgfoundry I'll add you to developer group for pstcollection Pavel > > > -- > Vincenzo Romano > NotOrAnd Information Technologies > NON QVIETIS MARIBVS NAVTA PERITVS > -- 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] Variadic polymorpic functions
2010/1/25 Pavel Stehule : > 2010/1/25 Vincenzo Romano : >> 2010/1/23 Pavel Stehule : >>> 2010/1/22 Vincenzo Romano : 2010/1/22 Tom Lane : > Vincenzo Romano writes: >> 2010/1/22 Tom Lane : >>> regression=# CREATE FUNCTION q( fmt text, variadic args "any" ) > >> And this would allow for a stdarg-like argument list? > > Yeah, it should work, given suitable C code. Great! >>> >>> I wrote this function year ago. >>> >>> look on content >>> >>> http://pgfoundry.org/projects/pstcollection/ >> >> Pavel, >> that format() function should be included into official contribs. >> What about HOWTO compile? > > There are not consensus about final semantic - some people prefer > sprintf like, some others PostgreSQL RAISE NOTICE like. so I'll keep > it outside. I looking on source of pstcollection - missing > documentation, missing regress test. I never rebuild it outside > PostgreSQL source tree - so it could be problem. Now: copy src to > contrib directory, make, make install - like standard contrib module. > > If you would to add some doc or notes, please, add it. I figured that out (from the Makefile) USE_PGXS=1 make install -- Vincenzo Romano NotOrAnd Information Technologies NON QVIETIS MARIBVS NAVTA PERITVS -- 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] Variadic polymorpic functions
2010/1/25 Vincenzo Romano : > 2010/1/23 Pavel Stehule : >> 2010/1/22 Vincenzo Romano : >>> 2010/1/22 Tom Lane : Vincenzo Romano writes: > 2010/1/22 Tom Lane : >> regression=# CREATE FUNCTION q( fmt text, variadic args "any" ) > And this would allow for a stdarg-like argument list? Yeah, it should work, given suitable C code. >>> >>> Great! >>> >> >> I wrote this function year ago. >> >> look on content >> >> http://pgfoundry.org/projects/pstcollection/ > > Pavel, > that format() function should be included into official contribs. > What about HOWTO compile? There are not consensus about final semantic - some people prefer sprintf like, some others PostgreSQL RAISE NOTICE like. so I'll keep it outside. I looking on source of pstcollection - missing documentation, missing regress test. I never rebuild it outside PostgreSQL source tree - so it could be problem. Now: copy src to contrib directory, make, make install - like standard contrib module. If you would to add some doc or notes, please, add it. Pavel > -- 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] Updates: all or partial records
Paul M Foster wrote: Scenario: You have to update a record. One or more fields are unchanged from the original record being altered. So you have two options: 1) Include those fields in your UPDATE statement, even though they are unchanged; 2) Omit unchanged fields from the UPDATE statement. My first inclination is to omit unchanged fields. However, I have the idea that PG simply marks the existing record to be dropped, and generates a whole new row by copying unspecified fields from the original record. My question is, which is more efficient? Performance-wise, does it matter whether unchanged fields are included or omitted on UPDATE statements my first order guess is, sending and having to parse the additional unchanged fields in your UPDATE statement is more expensive than letting the engine just copy them from the old tuple to the new. -- 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] Postgresql HA on MSCS over VMWARE
On Monday, January 25, 2010, Steeles wrote: > As title, please help. > > I want to setup Postgresql HA by MSCS in VMWARE platform. (win server 2003, > PG 8.3 on 32 bit) > > MSCS has been setup, the problem can't start postgresql service. > > PGDATA is on the shared disk. > > I tried generic service, and application, either one won't bring up > postgresql database engine service. > > did I do something wrong? > That should work. My guess is it's a permissions issue. Check what you get in your logs - both in the pg_log directory and in the windows event log. -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/ -- 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] Variadic polymorpic functions
2010/1/23 Pavel Stehule : > 2010/1/22 Vincenzo Romano : >> 2010/1/22 Tom Lane : >>> Vincenzo Romano writes: 2010/1/22 Tom Lane : > regression=# CREATE FUNCTION q( fmt text, variadic args "any" ) >>> And this would allow for a stdarg-like argument list? >>> >>> Yeah, it should work, given suitable C code. >> >> Great! >> > > I wrote this function year ago. > > look on content > > http://pgfoundry.org/projects/pstcollection/ Pavel, that format() function should be included into official contribs. What about HOWTO compile? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Updates: all or partial records
Scenario: You have to update a record. One or more fields are unchanged from the original record being altered. So you have two options: 1) Include those fields in your UPDATE statement, even though they are unchanged; 2) Omit unchanged fields from the UPDATE statement. My first inclination is to omit unchanged fields. However, I have the idea that PG simply marks the existing record to be dropped, and generates a whole new row by copying unspecified fields from the original record. My question is, which is more efficient? Performance-wise, does it matter whether unchanged fields are included or omitted on UPDATE statements? Paul -- Paul M. Foster -- 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] 100% of CPU utilization postgres process
Thanks, > How large is $PGDATA/global/pgstat.stat ? Unfortunately, the size of pgstat.stat was not taken when the CPU utilization of the postgress process reached nearly 100%... > If it's very large (many MB), try doing pg_stats_reset(). If that makes > the stats collector CPU usage drop, consider an update to PG 8.4.x, > which is more efficient at dealing with large stats files. Does it mean that CPU usage rate of stats collector process could possiblly reach 100% unless the postgres is updated to the version 8.4.x, instead of 8.3.x? (So far I'm planning to update the postgres to the version 8.3.9) Regards, _ 【節約!】インターネット代、見直しませんか? http://campaign.live.jp/eaccess/Top/
Re: [GENERAL] 100% of CPU utilization postgres process
> 8.3.3 is fairly old, they are up to 8.3.9 in that version. seee the > release notes for each version from 8.3.4 to 8.3.9 to see what bugs were > fixed... > http://www.postgresql.org/docs/current/static/release.html Thanks, I was planning to update the postgres to the newer version, but I was not sure if the problem would be solved or not since I couldn't find statement about stats collector ,as far as I read through the release notes for each version from 8.3.4 to 8.3.9. That's way I posted a question, hoping I could get any information... Regards, _ Windows 7とOfficeが安くなる!(ダウンロード版) http://promotion.live.jp/special/msstore/
Re: [GENERAL] 100% of CPU utilization postgres process
Hashimoto Yuya writes: > [ lots of time spent by stats collector process ] How large is $PGDATA/global/pgstat.stat ? If it's very large (many MB), try doing pg_stats_reset(). If that makes the stats collector CPU usage drop, consider an update to PG 8.4.x, which is more efficient at dealing with large stats files. 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] Postgresql HA on MSCS over VMWARE
As title, please help. I want to setup Postgresql HA by MSCS in VMWARE platform. (win server 2003, PG 8.3 on 32 bit) MSCS has been setup, the problem can't start postgresql service. PGDATA is on the shared disk. I tried generic service, and application, either one won't bring up postgresql database engine service. did I do something wrong? Thanks a bunch.
Re: [GENERAL] 100% of CPU utilization postgres process
Hashimoto Yuya wrote: -Postgres version : "PostgreSQL 8.3.3 on i386-portbld-freebsd7.0, compiled by GCC cc (GCC) 4.2.1 20070719 [FreeBSD]" 8.3.3 is fairly old, they are up to 8.3.9 in that version. seee the release notes for each version from 8.3.4 to 8.3.9 to see what bugs were fixed... http://www.postgresql.org/docs/current/static/release.html -disk configuration : 3 HITACHI 7,200rpm SATA disks in RAID5 raid 5 performs rather badly on database update kind of operations, especially without a battery backed writeback cache not saying this is your problem, but its not a real good idea. We use raid1+0 aka raid10 for our database volumes -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] 100% of CPU utilization postgres process
Hello, I observed the event that CPU utilization of the process related to postgres records almost 100% for unknown reason. It would be appreciated if any of you provide any information on this. The following line is a part of the result of "ps -auxeww". = pgsql 682 99.0 0.1 9336 2740 ?? Rs 27Nov08 343573:19.27 USER=pgsql MAIL=/var/mail/pgsql HOME=/usr/local/pgsql BLOCKSIZE=K PGLOCALEDIR=/usr/local/share/locale PGSYSCONFDIR=/usr/local/etc/postgresql PATH=/sbin:/bin:/usr/sbin:/usr/bin:/usr/games:/usr/local/sbin:/usr/local/bin:/usr/local/pgsql/bin SHELL=/bin/sh PWD=/usr/local/pgsql FTP_PASSIVE_MODE=YES PGDATA=/sa/db postgres: stats collector process(postgres) = Judging from the result, I could see that stats collector process caused this unusually high CPU utilization rate. I found similar problem at http://archives.postgresql.org/pgsql-general/2008-06/msg00934.php, although there seemed no clear cause proven nor the statement that it's because of postgres bug. Also, the following message was seen in the postgres log. == Nov 12 02:49:53 postgres[681]: [2-1] WARNING: worker took too long to start; cancelled Nov 12 02:50:53 postgres[681]: [3-1] WARNING: worker took too long to start; cancelled … Nov 12 11:14:12 postgres[681]: [506-1] WARNING: worker took too long to start; cancelled Nov 12 11:15:12 postgres[681]: [507-1] WARNING: worker took too long to start; cancelled == Once the message which started with "postgres[xxx]" appeared, it had been repeated until the OS was manually shut down. I'm not sure if each of the two could happen separately nor if one of the two could trigger the other. Do any of you happen to know more than what was posted at http://archives.postgresql.org/pgsql-general/2008-06/msg00934.php ? The event above was observed under the condition as follows. -Postgres version : "PostgreSQL 8.3.3 on i386-portbld-freebsd7.0, compiled by GCC cc (GCC) 4.2.1 20070719 [FreeBSD]" (It was installed via ports.) -To connect the PostgreSQL Database, the following drivers are used. php5-pdo_pgsql-5.2.12 p5-DBD-Pg-2.16.0 -Operating system and version ・FreeBSD 7.0-RELEASE-p2 -Hardware ・CPU : Intel, Xeon2.4 ・RAM : 2GB RAM ・Storage RAID controller : LSI MegaRAID -battery backed cache : none -write-back : disabled -Software RAID : not used -SAN : not used -disk configuration : 3 HITACHI 7,200rpm SATA disks in RAID5 - filesystem : ufs Regards, _ 【節約!】インターネット代、見直しませんか? http://campaign.live.jp/eaccess/Top/
Re: [GENERAL] \dt+ sizes don't include TOAST data
Tom Lane wrote: > Greg Smith writes: > > Florian Weimer wrote: > >> The sizes displayed by \dt+ in version 8.4.2 do not take TOAST tables > >> into account, presumably because the pg_relation_size does not reflect > >> that, either. I think this is a bit surprising. From a user > >> perspective, these are part of the table storage (I understand that > >> the indices might be a different story, but TOAST table are a fairly > >> deep implementation detail and should perhaps be hidden here). > > > As of last week there's a new pg_table_size available that does what you > > want here: > > http://archives.postgresql.org/pgsql-committers/2010-01/msg00288.php > > > I don't believe \dt+ has been updated yet to use that though; that's > > worth considering for a minute, not sure anybody thought about it yet. > > We could only use pg_table_size against a backend >= 9.0, which would > mean that the displayed results mean something different depending on > which backend version psql is being used with. That's not necessarily > a deal-breaker, but it does seem a bit evil. Perhaps we can emulate pg_table_size on earlier server versions, using a query which provides the sum of table plus toast items. It would be a bit slower, but the normal case of using the same server version would be fast. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- 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] \dt+ sizes don't include TOAST data
Greg Smith writes: > Florian Weimer wrote: >> The sizes displayed by \dt+ in version 8.4.2 do not take TOAST tables >> into account, presumably because the pg_relation_size does not reflect >> that, either. I think this is a bit surprising. From a user >> perspective, these are part of the table storage (I understand that >> the indices might be a different story, but TOAST table are a fairly >> deep implementation detail and should perhaps be hidden here). > As of last week there's a new pg_table_size available that does what you > want here: > http://archives.postgresql.org/pgsql-committers/2010-01/msg00288.php > I don't believe \dt+ has been updated yet to use that though; that's > worth considering for a minute, not sure anybody thought about it yet. We could only use pg_table_size against a backend >= 9.0, which would mean that the displayed results mean something different depending on which backend version psql is being used with. That's not necessarily a deal-breaker, but it does seem a bit evil. An alternative worth thinking about is to make it use pg_total_relation_size instead of pg_relation_size. That's available, with similar semantics, in all versions that have pg_relation_size either (ie, >= 8.1). Also, this is arguably more nearly the right thing since at the level of \dt+ I think people would expect indexes to get folded in too. 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: Fwd: [GENERAL] [LibPQ] Trying PQconnectStart: it doesn't seem to connect
Alessandro Agosto wrote: > I'm not yet within select/poll cycle, this is the first call that should > return CONNECTION_OK or CONNECTION_BAD (refering to docs). That would be the behavior of PQconnectdb(), not PQconnectStart(). Have you read that part of the doc: Other states might also occur during (and only during) an asynchronous connection procedure. These indicate the current stage of the connection procedure and might be useful to provide feedback to the user for example. These statuses are: CONNECTION_STARTED Waiting for connection to be made. CONNECTION_MADE Connection OK; waiting to send. CONNECTION_AWAITING_RESPONSE Waiting for a response from the server. CONNECTION_AUTH_OK Received authentication; waiting for backend start-up to finish. CONNECTION_SSL_STARTUP Negotiating SSL encryption. CONNECTION_SETENV Negotiating environment-driven parameter settings. Note that, although these constants will remain (in order to maintain compatibility), an application should never rely upon these occurring in a particular order, or at all, or on the status always being one of these documented values. An application might do something like this: switch(PQstatus(conn)) { case CONNECTION_STARTED: feedback = "Connecting..."; break; case CONNECTION_MADE: feedback = "Connected to server..."; break; -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Fwd: [GENERAL] [LibPQ] Trying PQconnectStart: it doesn't seem to connect
From: Alessandro Agosto Date: 2010/1/24 Subject: Re: [GENERAL] [LibPQ] Trying PQconnectStart: it doesn't seem to connect To: Craig Ringer Hi, thank you for your reply. 2010/1/24 Craig Ringer What's wrong with psycopg2 for this purpose? > > Python's threading support (in the standard CPython interpreter) is awful > due to the global interpreter lock. However, it works for waiting on > blocking sockets as the GIL is released before entering most C-language > routines. So you should be able to use psycopg2 in dedicated I/O worker > threads just fine. > > If you're trying to use non-blocking sockets and select(...) with libpq, > well, _then_ you'll have to go outside psycopg2. Be aware, though, that > using SSL sockets in a non-blocking manner can be ... complicated ... so > look into that in detail before deciding on this path. Multiple threads with > blocking connections is likely to be a LOT easier. > Yes, i know psycopg2 and i like it. But as you said i'm trying to use non-blocking socket and epoll (but writing a mock up i can use poll and work on events later). Honestly i'm not aware on every problem that async programming comport (like specific ssl problems). Anyway multiplexing programming is, yes complicated, but also powerful and a program that uses multiplexing has a lower footprint than thread/fork programs. In python, as you said there is the "evil" GIL. Frankly, though, you're never going to get wonderful results out of this. > Twisted tries, but you've probably seen the issues it has working around the > GIL and the limited success it has doing so. CPython's GIL dooms it to be a > pretty shoddy language for high concurrency use, whether you use an async > socket server model or a blocking threaded model. > I know twisted but sincerely i don't like to write code that makes a lot of callbacks, but this is my personal opinion. I've used it a lot of times. Probably you are right but on network programming async servers are faster than classic threaded versions, also if mean more problems. The lack of an asynchronous database interface is a problem that i could solve using more processes of my webserver but this isn't a real solution. So i'm on this project. Can someone tell me what i wrong? And if you know some resource that > explains with an example how i should to verify the connection during > the select/poll, would be appreciated. > You can't reliably verify that a connection is alive with select/poll. > TCP/IP timeouts are very long and until the connection times out at the > TCP/IP level, it might appear fine even though the peer died hours ago. > I'm not yet within select/poll cycle, this is the first call that should return CONNECTION_OK or CONNECTION_BAD (refering to docs). Probably i wrong but this call seems to return another status, or as you said i cannot know (also if i'm out of any cycle) if the connection is ok or not. If the async apis became a problem , can i call the *synchronous* apis through my webserver's main event loop? This should work or not? (i mean to make the connection asynchronous). Thank you again, Greetings. -- Alessandro A. -- Alessandro A.
Re: [GENERAL] Recursion in triggers?
"Gauthier, Dave" writes: > Ya, I worded the original poorley. Let me try again > The after update trigger on the table sets some of the NEW.column values for > record A. Then it executes another update on the same table, but on record > B. That second execution of the update trigger needs to see the mods made to > record A. Changing NEW in an after trigger has no effect outside the trigger function itself. It's too late to affect the data that went into the table --- that's more or less the whole point of AFTER vs BEFORE triggers. However, once you get that issue straightened out, it is true that triggers fired pursuant to the UPDATE inside the first trigger will also see whatever data changes the first trigger saw. See http://www.postgresql.org/docs/8.4/static/trigger-datachanges.html 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] port question
Amy Smith wrote: I have installed a v8.4 and first port using localhost is ok. but the second one using different port will get error when connect using IP address. Is that only one port is allowed for one server ? please help - need expert's advice. you can run different instances of postgresql on different ports as long as their $PGDATA directories are different. is this second server configured to LISTEN_ADDRESSES='*' ? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] port question
I have installed a v8.4 and first port using localhost is ok. but the second one using different port will get error when connect using IP address. Is that only one port is allowed for one server ? please help - need expert's advice. thanks Amy
[GENERAL] postgres
All how to get rid of the postmaster that is still running, but I deleted the $PGDATA cluster file, so it can not stop it. but I can not use the port again for new cluster. PLEASAE HELP. Amy
Re: [GENERAL] How to use PG_DUMP?
Hi, I have tested with "pg_dump -u -p 5432 -d -f c:\test.sql mydatabase" but the order of the INSERTS it is not the correct. What PG_DUMP does is to add the CONSTRAINTS after doing the INSERTS. There is a way to have the correct order of the INSERTS? I have tested the COPY but does not insert nothing. What can I do? Best Regards, On Sun, Jan 24, 2010 at 5:22 PM, Tom Lane wrote: > Andre Lopes writes: > > I have generated the CREATE statements using a modeling tool, and I have > > created another database to test the changes. My problem is the order of > the > > INSERT statements generated by PG_DUMP [-a -d], causing errors because of > > the order of the INSERTS. > > If you are talking about foreign key constraint failures, the best way > is to use a regular dump (not -a). pg_dump will order the data and > constraint creations properly then. > >regards, tom lane >
Re: [GENERAL] Recursion in triggers?
Ya, I worded the original poorley. Let me try again The after update trigger on the table sets some of the NEW.column values for record A. Then it executes another update on the same table, but on record B. That second execution of the update trigger needs to see the mods made to record A. One table being updated, calling more updates in the after trigger, needs to see the mods made in the previous update trigger executions. It's like a cascading operation, but on just one table. Sorry about the confussion, and thanks for the help. -Original Message- From: Craig Ringer [mailto:cr...@postnewspapers.com.au] Sent: Sunday, January 24, 2010 11:34 AM To: Gauthier, Dave Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Recursion in triggers? Gauthier, Dave wrote: > What I need to know is if, in the "after" update trigger I make the > subsequent updates to other records in the same table, with the OLD/NEW > record ponters be set properly in those subsequent update trigger > invocations? They'll be set properly. I'm not sure they'll be set how you want/expect, though. > Will the current and modified NEW.* values be passed down > into the next update trigger "before" call as OLD.* values? Eh? Why would they? Your AFTER trigger when fired modifies a different record, right, rather than modifying the same one again. So why would the `NEW' and `OLD' variables in the second firing have anything to do with those in the first firing? (Or have I misunderstood what you're trying to ask?) Is your question really one about when the changes become visible? Ie, if an AFTER UPDATE ... FOR EACH ROW trigger performs an UPDATE on a table, does the second instance of that same trigger procedure see the changes made to the first row? That I can't give you a guaranteed-accurate answer to right now (though "I think so") ... but suggest that writing a simple test case would be a a pretty conclusive way to find out if the docs are unclear. -- Craig Ringer -- 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 use PG_DUMP?
Andre Lopes writes: > I have generated the CREATE statements using a modeling tool, and I have > created another database to test the changes. My problem is the order of the > INSERT statements generated by PG_DUMP [-a -d], causing errors because of > the order of the INSERTS. If you are talking about foreign key constraint failures, the best way is to use a regular dump (not -a). pg_dump will order the data and constraint creations properly then. 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] [HACKERS] Sugerencia de opcion
Robert Haas writes: > 2009/1/22 Informatica-Cooperativa Cnel. Oviedo : >> SELECT id, sum(salario) as SumaSalario >> FROM salarios >> GROUP BY id >> HAVING SumaSalario>500; > I've wished for that syntax once or twice myself, but I'm assuming > there's a reason we haven't implemented it? It's contrary to standard. There are some other reasons you can find in the archives, too. 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] Recursion in triggers?
Gauthier, Dave wrote: > What I need to know is if, in the “after” update trigger I make the > subsequent updates to other records in the same table, with the OLD/NEW > record ponters be set properly in those subsequent update trigger > invocations? They'll be set properly. I'm not sure they'll be set how you want/expect, though. > Will the current and modified NEW.* values be passed down > into the next update trigger “before” call as OLD.* values? Eh? Why would they? Your AFTER trigger when fired modifies a different record, right, rather than modifying the same one again. So why would the `NEW' and `OLD' variables in the second firing have anything to do with those in the first firing? (Or have I misunderstood what you're trying to ask?) Is your question really one about when the changes become visible? Ie, if an AFTER UPDATE ... FOR EACH ROW trigger performs an UPDATE on a table, does the second instance of that same trigger procedure see the changes made to the first row? That I can't give you a guaranteed-accurate answer to right now (though "I think so") ... but suggest that writing a simple test case would be a a pretty conclusive way to find out if the docs are unclear. -- Craig Ringer -- 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] Referencing to system catalog problem
Thank you Adrian. I apparently missed that post. Guess I will have to come up with an different approach. "Adrian Klaver" wrote in message news:201001231002.15874.adrian.kla...@gmail.com... > On Saturday 23 January 2010 6:15:36 am Davor J. wrote: >> I am logged in as superuser. I am trying to create something similar to >> this: >> >> >> Code: >> CREATE TABLE tbl_unit_convfunctions( >> unit_from integer REFERENCES tbl_units (unit_id), >> unit_to integer REFERENCES tbl_units (unit_id), >> proc_id oid REFERENCES pg_proc (oid) >> )but no matter what I refer to from pg_proc, i get the error message: >> >> ERROR: permission denied: "pg_proc" is a system catalog >> SQL state: 42501 >> >> Has anyone any suggestions how to do something similar, or even better: >> how >> to solve this error. I couldn't find any useful information on the net >> about this issue. >> >> Thanks, >> Davor >> >> Original post: >> http://forums.devshed.com/postgresql-help-21/referencing-to-system-catalog- >>problem-670063.html >> >> Note: OID's are unique in that table, and should be referable, and I >> explicitely granted the REFERENCE priviledge to the superuser. > > > You can't have FKs to system tables. See this post for explanation: > http://archives.postgresql.org/pgsql-general/2004-12/msg00840.php > > -- > 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 > -- 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] Self-referential records
On Sunday 24. January 2010 16.22.00 Wayne E. Pfeffer wrote: > If you do not use null to represent a root node, when you go to unwind the > data from the table to generate a hierarchy tree, you could end up with an > infinite loop. The query will always be looking for the next parent in the > hierarchy. Meaning, you will want to find the parent of a node using the > given parent_id, the query will find the parent of 1 to be 1, then it will > look again for the parent of 1 it will find 1, etc. etc. ad nauseum. I enjoy > using recursion as much as the next guy, but this could cause some serious > issues with the PostgreSQL query engine eating up system resources. It doesn't really matter if the root node is NULL or 0 or whatever. You just have to realize that the root node is a special case and program accordingly. An adjacency tree is not a normalized structure, and will never be. It's the programmer's responsibility to ensure that circular references can't occur. regards, -- Leif Biberg Kristensen http://solumslekt.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] Self-referential records
If you do not use null to represent a root node, when you go to unwind the data from the table to generate a hierarchy tree, you could end up with an infinite loop. The query will always be looking for the next parent in the hierarchy. Meaning, you will want to find the parent of a node using the given parent_id, the query will find the parent of 1 to be 1, then it will look again for the parent of 1 it will find 1, etc. etc. ad nauseum. I enjoy using recursion as much as the next guy, but this could cause some serious issues with the PostgreSQL query engine eating up system resources. Wayne E. Pfeffer On Sun, Jan 24, 2010 at 9:13 AM, Andreas Kretschmer < akretsch...@spamfence.net> wrote: > Xi Shen wrote: > > > what if you insert other values like '1', '999'? will the insertion > > successful? if so, what's the difference between a deferred reference > > and no reference at all? > > Nice question ;-) > > Okay, recreate the table but without NOT NULL: > > test=# CREATE TABLE refers ( id SERIAL PRIMARY KEY, name VARCHAR(255) NOT > NULL, parent_id INTEGER , FOREIGN KEY (parent_id) REFERENCES refers(id) > deferrable initially deferred); > NOTICE: CREATE TABLE will create implicit sequence "refers_id_seq" for > serial column "refers.id" > NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index > "refers_pkey" for table "refers" > CREATE TABLE > Zeit: 63,477 ms > test=*# insert into refers (name, parent_id) values ('xxx',null); > INSERT 0 1 > Zeit: 0,686 ms > > I think, NULL is more sensible than inserting a 'random' value like 0. > > > > Andreas > -- > Really, I'm not out to destroy Microsoft. That will just be a completely > unintentional side effect. (Linus Torvalds) > "If I was god, I would recompile penguin with --enable-fly." (unknown) > Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889° > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- Red Leader 1 -- Out URL: http://apps.facebook.com/faceblogged/?uid=674333666
Re: [GENERAL] Self-referential records
Xi Shen wrote: > what if you insert other values like '1', '999'? will the insertion > successful? if so, what's the difference between a deferred reference > and no reference at all? Nice question ;-) Okay, recreate the table but without NOT NULL: test=# CREATE TABLE refers ( id SERIAL PRIMARY KEY, name VARCHAR(255) NOT NULL, parent_id INTEGER , FOREIGN KEY (parent_id) REFERENCES refers(id) deferrable initially deferred); NOTICE: CREATE TABLE will create implicit sequence "refers_id_seq" for serial column "refers.id" NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "refers_pkey" for table "refers" CREATE TABLE Zeit: 63,477 ms test=*# insert into refers (name, parent_id) values ('xxx',null); INSERT 0 1 Zeit: 0,686 ms I think, NULL is more sensible than inserting a 'random' value like 0. Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) "If I was god, I would recompile penguin with --enable-fly." (unknown) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889° -- 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] Self-referential records
What is the preferred way to enforce that there is at least one orphan record if any at all, and that a record is not a Marty McFly type descendent of itself? I would suggest that a statement level after trigger is the way to go, but I myself have never actually had to enforce this. Regards, Peter Geoghegan -- 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] Self-referential records
On Sun, Jan 24, 2010 at 10:36 PM, Andreas Kretschmer wrote: > Ovid wrote: > >> Assuming I have the following table: >> >> CREATE TABLE refers ( >> id SERIAL PRIMARY KEY, >> name VARCHAR(255) NOT NULL, >> parent_id INTEGER NOT NULL, >> FOREIGN KEY (parent_id) REFERENCES refers(id) >> ); >> I need to insert two records so that "select * from refers" looks like this: >> >> =# select * from refers; >> id | name | parent_id >> +--+--- >> 1 | | 1 >> 2 | yyy | 2 > > I think you mean id=1, parent_id=2 and id=2, parent_id=1, or? > >> >> The first record can't be inserted because I don't yet know the parent_id. >> The second record can be inserted after the first, but I since this is >> merely a large .sql file that I intend to shove into the PG, I'd much rather >> declare a variable in the script to get this done. I'm thinking something >> like the following pseudo-code: >> >> INSERT INTO refers (name, parent_id) VALUES ('', :id); >> SELECT id INTO :parent_id FROM refers WHERE name=''; >> INSERT INTO refers (name, parent_id) VALUES ('yyy', :parent_id); >> >> Obviously the above is gibberish, but hopefully it makes clear what I'm >> trying to do :) >> >> Oh, and "parent_id" is NOT NULL because I hate the logical inconsistencies >> associated with NULL values. > > To handle that you can set the constzraint deferrable, initially > deferred: > > test=# CREATE TABLE refers ( id SERIAL PRIMARY KEY, name VARCHAR(255) NOT > NULL, parent_id INTEGER NOT NULL, FOREIGN KEY (parent_id) REFERENCES > refers(id) deferrable initially deferred); > NOTICE: CREATE TABLE will create implicit sequence "refers_id_seq" for > serial column "refers.id" > NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "refers_pkey" > for table "refers" > CREATE TABLE > Zeit: 25,599 ms > test=*# insert into refers (name, parent_id) values ('xxx',0); > INSERT 0 1 > Zeit: 0,662 ms to Andreas, this 'deferrable' thing is really cool. but i have a question. at this point, where i insert this text, if i select the refers table, what would the parent_id looks like? > test=*# insert into refers (name, parent_id) select 'yyy', id from refers > where name = 'xxx'; > INSERT 0 1 > Zeit: 0,436 ms > test=*# update refers set parent_id = (select id from refers where name = > 'yyy') where name = 'xxx'; > UPDATE 1 > Zeit: 0,431 ms > test=*# select * from refers; > id | name | parent_id > +--+--- > 2 | yyy | 1 > 1 | xxx | 2 > (2 Zeilen) > > > The next release 9.0 contains (i hope) writes CTE, with this featue you can > do: > > test=# CREATE TABLE refers ( id SERIAL PRIMARY KEY, name VARCHAR(255) NOT > NULL, parent_id INTEGER NOT NULL, FOREIGN KEY (parent_id) REFERENCES > refers(id) deferrable initially deferred); > NOTICE: CREATE TABLE will create implicit sequence "refers_id_seq" for > serial column "refers.id" > NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "refers_pkey" > for table "refers" > CREATE TABLE > Time: 3,753 ms > test=*# > > with > t1 as (select nextval('refers_id_seq') as id), > t2 as (insert into refers (id, name, parent_id) select > nextval('refers_id_seq'), 'yyy', t1.id from t1 returning *), > t3 as (insert into refers (id, name, parent_id) select t1.id, 'xxx', t2.id > from t1, t2) > select true; > bool > -- > t > (1 row) > > Time: 0,853 ms > test=*# select * from refers; > id | name | parent_id > +--+--- > 2 | yyy | 1 > 1 | xxx | 2 > (2 rows) > > > That's (the two insert's) are now one single statement ;-) > > > Andreas > -- > Really, I'm not out to destroy Microsoft. That will just be a completely > unintentional side effect. (Linus Torvalds) > "If I was god, I would recompile penguin with --enable-fly." (unknown) > Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889° > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > to Ovid, if you are going to create a tree in the table, you should certainly allow the parent_id be null, otherwise, what would be the parent of root? if you are meant to create a circle...god, what is that for? may i have your story? -- Best Regards, David Shen http://twitter.com/davidshen84/ http://meme.yahoo.com/davidshen84/ -- 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] [HACKERS] Sugerencia de opcion
2009/1/22 Informatica-Cooperativa Cnel. Oviedo : > Buenos Dias todos, > > Soy un usuario de postgres de Paraguay, consulto > sobre la posibilidad de inclucion en la futura version la siguiente > sentencia(Uso de alias en la condicion HAVING ): > > > SELECT id, sum(salario) as SumaSalario > FROM salarios > GROUP BY id > HAVING SumaSalario>500; I've wished for that syntax once or twice myself, but I'm assuming there's a reason we haven't implemented it? Part of the problem is it's inheritantly ambiguous if salarios happens to contain a column called sumasalario, which is a problem that seems to arise for me fairly regularly in practice. Still, it would be nice for WHERE/GROUP BY/HAVING clauses to have an explicit way to reference "the target list column called foo". ...Robert -- 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] Self-referential records
Xi Shen wrote: > > To handle that you can set the constzraint deferrable, initially > > deferred: > > > > test=# CREATE TABLE refers ( id SERIAL PRIMARY KEY, name VARCHAR(255) NOT > > NULL, parent_id INTEGER NOT NULL, FOREIGN KEY (parent_id) REFERENCES > > refers(id) deferrable initially deferred); > > NOTICE: CREATE TABLE will create implicit sequence "refers_id_seq" for > > serial column "refers.id" > > NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index > > "refers_pkey" for table "refers" > > CREATE TABLE > > Zeit: 25,599 ms > > test=*# insert into refers (name, parent_id) values ('xxx',0); > > INSERT 0 1 > > Zeit: 0,662 ms > > to Andreas, > > this 'deferrable' thing is really cool. but i have a question. at this > point, where i insert this text, if i select the refers table, what > would the parent_id looks like? 0, as i inserted: test=*# insert into refers (name, parent_id) values ('xxx',0); INSERT 0 1 Zeit: 0,636 ms test=*# select * from refers; id | name | parent_id +--+--- 1 | xxx | 0 (1 Zeile) Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) "If I was god, I would recompile penguin with --enable-fly." (unknown) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889° -- 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] Self-referential records
Ovid wrote: > Assuming I have the following table: > > CREATE TABLE refers ( > idSERIAL PRIMARY KEY, > name VARCHAR(255) NOT NULL, > parent_id INTEGER NOT NULL, > FOREIGN KEY (parent_id) REFERENCES refers(id) > ); > I need to insert two records so that "select * from refers" looks like this: > > =# select * from refers; > id | name | parent_id > +--+--- > 1 | | 1 > 2 | yyy | 2 I think you mean id=1, parent_id=2 and id=2, parent_id=1, or? > > The first record can't be inserted because I don't yet know the parent_id. > The second record can be inserted after the first, but I since this is merely > a large .sql file that I intend to shove into the PG, I'd much rather declare > a variable in the script to get this done. I'm thinking something like the > following pseudo-code: > > INSERT INTO refers (name, parent_id) VALUES ('', :id); > SELECT id INTO :parent_id FROM refers WHERE name=''; > INSERT INTO refers (name, parent_id) VALUES ('yyy', :parent_id); > > Obviously the above is gibberish, but hopefully it makes clear what I'm > trying to do :) > > Oh, and "parent_id" is NOT NULL because I hate the logical inconsistencies > associated with NULL values. To handle that you can set the constzraint deferrable, initially deferred: test=# CREATE TABLE refers ( id SERIAL PRIMARY KEY, name VARCHAR(255) NOT NULL, parent_id INTEGER NOT NULL, FOREIGN KEY (parent_id) REFERENCES refers(id) deferrable initially deferred); NOTICE: CREATE TABLE will create implicit sequence "refers_id_seq" for serial column "refers.id" NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "refers_pkey" for table "refers" CREATE TABLE Zeit: 25,599 ms test=*# insert into refers (name, parent_id) values ('xxx',0); INSERT 0 1 Zeit: 0,662 ms test=*# insert into refers (name, parent_id) select 'yyy', id from refers where name = 'xxx'; INSERT 0 1 Zeit: 0,436 ms test=*# update refers set parent_id = (select id from refers where name = 'yyy') where name = 'xxx'; UPDATE 1 Zeit: 0,431 ms test=*# select * from refers; id | name | parent_id +--+--- 2 | yyy | 1 1 | xxx | 2 (2 Zeilen) The next release 9.0 contains (i hope) writes CTE, with this featue you can do: test=# CREATE TABLE refers ( id SERIAL PRIMARY KEY, name VARCHAR(255) NOT NULL, parent_id INTEGER NOT NULL, FOREIGN KEY (parent_id) REFERENCES refers(id) deferrable initially deferred); NOTICE: CREATE TABLE will create implicit sequence "refers_id_seq" for serial column "refers.id" NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "refers_pkey" for table "refers" CREATE TABLE Time: 3,753 ms test=*# with t1 as (select nextval('refers_id_seq') as id), t2 as (insert into refers (id, name, parent_id) select nextval('refers_id_seq'), 'yyy', t1.id from t1 returning *), t3 as (insert into refers (id, name, parent_id) select t1.id, 'xxx', t2.id from t1, t2) select true; bool -- t (1 row) Time: 0,853 ms test=*# select * from refers; id | name | parent_id +--+--- 2 | yyy | 1 1 | xxx | 2 (2 rows) That's (the two insert's) are now one single statement ;-) Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) "If I was god, I would recompile penguin with --enable-fly." (unknown) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889° -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] How to use PG_DUMP?
Hi, I'am having trouble using PG_DUMP. The problem is the following, I have made some minor changes to my database, I have added one table. I have generated the CREATE statements using a modeling tool, and I have created another database to test the changes. My problem is the order of the INSERT statements generated by PG_DUMP [-a -d], causing errors because of the order of the INSERTS. I have tested PG_DUMP [-a] that creates the COPY statements, but no data is added to the tables. How can I solve this? Best Regards,
Re: [GENERAL] Self-referential records
Ovid wrote on 24.01.2010 14:43: Assuming I have the following table: CREATE TABLE refers ( idSERIAL PRIMARY KEY, name VARCHAR(255) NOT NULL, parent_id INTEGER NOT NULL, FOREIGN KEY (parent_id) REFERENCES refers(id) ); I need to insert two records so that "select * from refers" looks like this: =# select * from refers; id | name | parent_id +--+--- 1 | | 1 2 | yyy | 2 The first record can't be inserted because I don't yet know the parent_id. I ususally identify the root record by setting the parent_id to NULL. In my experience creating a cycle in the tree creates a lot of trouble that is hard to come by. Thomas -- 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] Self-referential records
On 1/24/10 8:43 AM, Ovid wrote: Assuming I have the following table: CREATE TABLE refers ( idSERIAL PRIMARY KEY, name VARCHAR(255) NOT NULL, parent_id INTEGER NOT NULL, FOREIGN KEY (parent_id) REFERENCES refers(id) ); I need to insert two records so that "select * from refers" looks like this: =# select * from refers; id | name | parent_id +--+--- 1 | | 1 2 | yyy | 2 The first record can't be inserted because I don't yet know the parent_id. The second record can be inserted after the first, but I since this is merely a large .sql file that I intend to shove into the PG, I'd much rather declare a variable in the script to get this done. I'm thinking something like the following pseudo-code: INSERT INTO refers (name, parent_id) VALUES ('', :id); SELECT id INTO :parent_id FROM refers WHERE name=''; INSERT INTO refers (name, parent_id) VALUES ('yyy', :parent_id); Obviously the above is gibberish, but hopefully it makes clear what I'm trying to do :) Oh, and "parent_id" is NOT NULL because I hate the logical inconsistencies associated with NULL values. You could always remove the NOT NULL or the FOREIGN KEY constraints during data load, then add them back on afterward. If the problem is with everyday usage and not just data load, you can still do this trick, since DDL can be transactionalized (is that a word). Just start a transaction, remove the NOT NULL constraint, add your new records, then update the parent_key as appropriate, then add the NOT NULL back. If any point during the process fails, just rollback the transaction. You may want to set the isolation level to serializable, but I'm not sure if that's necessary. -Bill -- 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] Self-referential records
On Sunday 24. January 2010 14.43.10 Ovid wrote: > Assuming I have the following table: > > CREATE TABLE refers ( > idSERIAL PRIMARY KEY, > name VARCHAR(255) NOT NULL, > parent_id INTEGER NOT NULL, > FOREIGN KEY (parent_id) REFERENCES refers(id) > ); > I need to insert two records so that "select * from refers" looks like this: > > =# select * from refers; > id | name | parent_id > +--+--- > 1 | | 1 > 2 | yyy | 2 > > The first record can't be inserted because I don't yet know the parent_id. I've got a similar structure. I just declared the root node with both id and parent_id=0. regards, -- Leif Biberg Kristensen http://solumslekt.org/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Self-referential records
Assuming I have the following table: CREATE TABLE refers ( idSERIAL PRIMARY KEY, name VARCHAR(255) NOT NULL, parent_id INTEGER NOT NULL, FOREIGN KEY (parent_id) REFERENCES refers(id) ); I need to insert two records so that "select * from refers" looks like this: =# select * from refers; id | name | parent_id +--+--- 1 | | 1 2 | yyy | 2 The first record can't be inserted because I don't yet know the parent_id. The second record can be inserted after the first, but I since this is merely a large .sql file that I intend to shove into the PG, I'd much rather declare a variable in the script to get this done. I'm thinking something like the following pseudo-code: INSERT INTO refers (name, parent_id) VALUES ('', :id); SELECT id INTO :parent_id FROM refers WHERE name=''; INSERT INTO refers (name, parent_id) VALUES ('yyy', :parent_id); Obviously the above is gibberish, but hopefully it makes clear what I'm trying to do :) Oh, and "parent_id" is NOT NULL because I hate the logical inconsistencies associated with NULL values. Cheers, Ovid-- Buy the book - http://www.oreilly.com/catalog/perlhks/ Tech blog- http://use.perl.org/~Ovid/journal/ Twitter - http://twitter.com/OvidPerl Official Perl 6 Wiki - http://www.perlfoundation.org/perl6 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Questions about connection clean-up and "invalid page header"
Hi Everybody. I have two questions. 1. We have a system that is accessed by Crystal reports which is in turned controlled by another (3rd party) system. Now, when a report takes too long or the user cancels it, it doesn't send a cancel request to Postgres. It just kills the Crystal process that works on it. As a result, the query is left alive on the Postgres backend. Eventually I get the message "Unexpected End of file" and the query is cancelled. But this doesn't happen soon enough for me - these are usually very heavy queries, and I'd like them to be cleaned up as soon as possible if the client connection has ended. Is there a parameter to set in the configuration or some other means to shorten the time before an abandoned backend's query is cancelled? 2. I get the following message in my development database: vacuumdb: vacuuming of database "reports" failed: ERROR: invalid page header in block 6200 of relation "rb" I had this already a couple of months ago. Looking around the web, I saw this error is supposed to indicate a hardware error. I informed my sysadmin, but since this is just the dev system and the data was not important, I did a TRUNCATE TABLE on the "rb" relation, and the errors stopped... But now the error is back, and I'm a bit suspicious. If this is a hardware issue, it's rather suspicious that it returned in the exact same relation after I did a "truncate table". I have many other relations in the system, ones that fill up a lot faster. So I suspect this might be a PostgreSQL issue after all. What can I do about this? We are currently using PostgreSQL v. 8.3.1 on the server side. TIA, Herouth -- 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] Recursion in triggers?
On 24 Jan 2010, at 5:36, Gauthier, Dave wrote: > Hi: > > I’m dealing with a hierarchical design where changes in one record can and > should cause changes in other records lower inthe hierarchy. I’m trying to > use update triggers to do this. And recursion would be a real nice way to > do this. > > What I need to know is if, in the “after” update trigger I make the > subsequent updates to other records in the same table, with the OLD/NEW > record ponters be set properly in those subsequent update trigger > invocations? Will the current and modified NEW.* values be passed down into > the next update trigger “before” call as OLD.* values? Or is recursion like > this not allowed? I'm not really sure what you're trying to do, so it's a tad hard to answer. Are you using multiple before-update triggers on the SAME table? In that case you ask an interesting question that I don't know the answer to either. I do know that they'll fire ordered alphabetically on trigger name. A test case with a few raise notices is easily created though: BEGIN; CREATE FUNCTION x() RETURNS trigger AS $$ BEGIN RAISE NOTICE 'OLD.test = %, NEW.test = %', OLD.test, NEW.test; NEW.test := New.test + 1; RETURN NEW; END; $$ LANGUAGE plpgsql; CREATE TABLE trigger_test(test int); INSERT INTO trigger_test VALUES (1); CREATE TRIGGER a BEFORE UPDATE ON trigger_test FOR EACH ROW EXECUTE PROCEDURE x(); CREATE TRIGGER b BEFORE UPDATE ON trigger_test FOR EACH ROW EXECUTE PROCEDURE x(); SET client_min_messages TO notice; UPDATE trigger_test SET test=2 WHERE test=1; ROLLBACK; development=> \i /tmp/trigger_test.sql BEGIN CREATE FUNCTION CREATE TABLE INSERT 0 1 CREATE TRIGGER CREATE TRIGGER SET psql:/tmp/trigger_test.sql:26: NOTICE: OLD.test = 1, NEW.test = 2 psql:/tmp/trigger_test.sql:26: NOTICE: OLD.test = 1, NEW.test = 3 UPDATE 1 ROLLBACK Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll see there is no forest. !DSPAM:737,4b5c183b10607129821012! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general