[GENERAL] Functions - how to get the date created or updated
Hi Is there a pg_stat table that one can query, or a script to use to determine what date a function was created, or what date it was updated / modified? Ta Huda Booley DBA | CareerJunction | Better jobs. More often. Web: www.careerjunction.co.za |Email: h...@careerjunction.co.zamailto:h...@careerjunction.co.za Phone: +27 21 818 8635 | Mobile: +27 82 9587818 | Fax: +27 21 818 8609 Disclaimer This message contains information intended solely for the addressee, which is confidential or private in nature. If you are not the intended recipient, you may not peruse, use, disseminate, distribute or copy this message or any file attached to this message. Any such unauthorised use, is prohibited and may be unlawful. If you have received this message in error, please notify the sender immediately by e-mail, facsimile or telephone and thereafter delete the original message from your machine. Furthermore, the information contained in this message, and any attachments thereto, is for information purposes only and may contain the personal views and opinions of the author, which are not necessarily the views and opinions of CareerJunction or its subsidiaries and associated companies. CareerJunction therefore does not accept liability for any claims, loss or damages of whatsoever nature, arising as a result of the reliance on such information by anyone. Whilst all reasonable steps are taken to ensure the accuracy and integrity of information transmitted electronically and to preserve the confidentiality thereof, CareerJunction accepts no liability or responsibility whatsoever if information or data is, for whatever reason, incorrect, corrupted or does not reach its intended destination.
Re: [GENERAL] Functions - how to get the date created or updated
2010/1/14 Huda Booley (h...@careerjunction.co.za) h...@careerjunction.co.za: Hi Is there a pg_stat table that one can query, or a script to use to determine what date a function was created, or what date it was updated / modified? no regards Pavel Stehule Ta Huda Booley DBA | CareerJunction | Better jobs. More often. Web: www.careerjunction.co.za |Email: h...@careerjunction.co.za Phone: +27 21 818 8635 | Mobile: +27 82 9587818 | Fax: +27 21 818 8609 Disclaimer This message contains information intended solely for the addressee, which is confidential or private in nature. If you are not the intended recipient, you may not peruse, use, disseminate, distribute or copy this message or any file attached to this message. Any such unauthorised use, is prohibited and may be unlawful. If you have received this message in error, please notify the sender immediately by e-mail, facsimile or telephone and thereafter delete the original message from your machine. Furthermore, the information contained in this message, and any attachments thereto, is for information purposes only and may contain the personal views and opinions of the author, which are not necessarily the views and opinions of CareerJunction or its subsidiaries and associated companies. CareerJunction therefore does not accept liability for any claims, loss or damages of whatsoever nature, arising as a result of the reliance on such information by anyone. Whilst all reasonable steps are taken to ensure the accuracy and integrity of information transmitted electronically and to preserve the confidentiality thereof, CareerJunction accepts no liability or responsibility whatsoever if information or data is, for whatever reason, incorrect, corrupted or does not reach its intended destination. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
R: Re: R: Re: R: Re: [GENERAL] Weird EXECUTE ... USING behaviour
The documentation says (also in v8.5) These symbols refer to values supplied in the USING clause. valuesand not variable name or reference. This leads to the useful feature mentioned a line later in the same page. Once you have a value replaced you can avoid the restrictions you now mention on v8.5. What's the right place to submit proposals? Il giorno 14 gen, 2010 7:42 m., Tom Lane t...@sss.pgh.pa.us ha scritto: Scott Mead scott.li...@enterprisedb.com writes: Well it is in 8.5 Devel, so it could have bee... The particular paragraph mentioned was committed here http://archives.postgresql.org/pgsql-committers/2009-11/msg00094.php but as you note it was just a relocation of a comment that appeared elsewhere for at least two years before that (and even then, it was just documenting behavior that had existed since day one). regards, tom lane
[GENERAL] Memory Access Violation While Using PQexec
I encounter case when I call a stored procedure for 299166 th times (intensive,
i put a non-stop while true loop to call stored procedure)
, the following exception will be thrown from PQexec. I am rather sure the
exception are from PQexec, as there is a just before cout and just after cout
wrap around PQexec.
std::cout Start PQexec sql std::endl;
PGresult *res = PQexec(connection, sql.c_str());
std::cout End PQexec sql std::endl;
const bool status = (PQresultStatus(res) == PGRES_TUPLES_OK);
PQclear(res);
End PQexec... is not being printed.
The last printed message before crashed is :
Start PQexec SELECT * FROM insert_unit(1,299166,array[0.407461,0.516022,0.434267
,0.802882,-1,1.99054,1.99039,0.808687], array['Pad Area','Pad Pitch','Pad
Width','Pad Length','Side Pad Pitch','Package X Dimension','Package Y
Dimension','Package Z Dimension'],
array['mm','mm','mm','mm','mm','mm','mm','mm'])
The output is :
The thread 'Win32 Thread' (0x264) has exited with code 0 (0x0).
First-chance exception at 0x7c812afb in Report.exe: Microsoft C++ exception:
std::exception at memory location 0x0012edb4..
First-chance exception at 0x7c812afb in Report.exe: Microsoft C++ exception:
std::exception at memory location 0x0012ed94..
First-chance exception at 0x10017ea5 in Report.exe: 0xC005: Access
violation writing location 0xfeeefeee.
'Report.exe': Unloaded 'C:\WINDOWS\system32\wshtcpip.dll'
'Report.exe': Unloaded 'C:\WINDOWS\system32\hnetcfg.dll'
Unhandled exception at 0x10017ea5 in Report.exe: 0xC005: Access violation
writing location 0xfeeefeee.
The call stack is :
libpq.dll!10017ea5()
[Frames below may be incorrect and/or missing, no symbols loaded for
libpq.dll]
libpq.dll!1000fa61()
msvcr90d.dll!__set_flsgetvalue() Line 256 + 0xc bytes C
msvcp90d.dll!std::clog() + 0x3f bytes C++
Using VC 2008, I break at file tidtable.c
_CRTIMP PFLS_GETVALUE_FUNCTION __cdecl __set_flsgetvalue()
{
#ifdef _M_IX86
PFLS_GETVALUE_FUNCTION flsGetValue = FLS_GETVALUE; -- BREAK HERE
if (!flsGetValue)
{
flsGetValue = _decode_pointer(gpFlsGetValue);
TlsSetValue(__getvalueindex, flsGetValue);
}
return flsGetValue;
#else /* _M_IX86 */
Any suggestion?
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: R: Re: R: Re: [GENERAL] Weird EXECUTE ... USING behaviour
2010/1/14 Vincenzo Romano vincenzo.rom...@notorand.it: The documentation says (also in v8.5) These symbols refer to values supplied in the USING clause. valuesand not variable name or reference. This leads to the useful feature mentioned a line later in the same page. Once you have a value replaced you can avoid the restrictions you now mention on v8.5. What's the right place to submit proposals? it's conference pg_hackers. Pavel Stehule Il giorno 14 gen, 2010 7:42 m., Tom Lane t...@sss.pgh.pa.us ha scritto: Scott Mead scott.li...@enterprisedb.com writes: Well it is in 8.5 Devel, so it could have bee... The particular paragraph mentioned was committed here http://archives.postgresql.org/pgsql-committers/2009-11/msg00094.php but as you note it was just a relocation of a comment that appeared elsewhere for at least two years before that (and even then, it was just documenting behavior that had existed since day one). 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] Setting global parameter in Postgres 8.3
Hi All, Is there any way we can set the global parameter in Postgres 8.3? As I have a sql script which need to accept some parameter at run time. For example I have sql script for creating tablespaces called create_tablespace.sql Inside the sql script I have the following statements CREATE TABLESPACE bank_index_tbsp OWNER bank LOCATION '$drive/data/bank/index_tbsp'; CREATE TABLESPACE bank_master_tbsp OWNER bank LOCATION '$drive/data/bank/master_tbsp'; CREATE TABLESPACE bank_static_tbsp OWNER bank LOCATION '$drive /data/bank/static_tbsp'; I need to replace the $drive with a absoulte location in order to create the tablespaces at run time.So is there any way I can pass parameter to sql file at the time of execuitng it or is there any way I can set a global parameter which will repalce the $drive similar to the way postregs converts or maps the $libdir parameter to the absolute path of the Lib location of postgres. Any help and suggestion will be really helpful. Many thanks in advance
Re: [GENERAL] Setting global parameter in Postgres 8.3
In response to a.bhattacha...@sungard.com : CREATE TABLESPACE bank_master_tbsp OWNER bank LOCATION ?$drive/data/bank/ master_tbsp'; CREATE TABLESPACE bank_static_tbsp OWNER bank LOCATION ?$drive /data/bank/ static_tbsp'; I need to replace the $drive with a absoulte location in order to create the tablespaces at run time.So is there any way I can pass parameter to sql file at the time of execuitng it or is there any way I can set a global parameter which No. AFAIK. will repalce the $drive similar to the way postregs converts or maps the $libdir parameter to the absolute path of the ?Lib? location of postgres. Create a function for that ... Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: - Header) GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99 -- 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] Setting global parameter in Postgres 8.3
On Thu, Jan 14, 2010 at 11:10 AM, A. Kretschmer andreas.kretsch...@schollglas.com wrote: In response to a.bhattacha...@sungard.com : CREATE TABLESPACE bank_master_tbsp OWNER bank LOCATION ?$drive/data/bank/ master_tbsp'; CREATE TABLESPACE bank_static_tbsp OWNER bank LOCATION ?$drive /data/bank/ static_tbsp'; I need to replace the $drive with a absoulte location in order to create the tablespaces at run time.So is there any way I can pass parameter to sql file at the time of execuitng it or is there any way I can set a global parameter which You can't do in it plain sql, but if your script is designed especially for psql, then you could use a psql variable: http://www.postgresql.org/docs/current/static/app-psql.html#APP-PSQL-VARIABLES --Scott
[GENERAL] Configuration Optimisation
Hi, I am trying to optimise a database server to give the best performance possible, so I switched from windows 2k3 to linux (ubuntu 9.10) on the basis that most people seem to be of the opinion that postgres runs better on linux than windows. To test my optimisation of the system, I run a simple pgbench setup for 2 minutes. Not very scientific I realise but it has helped me tune systems quite well in the past. My problem is that I am getting nowhere near the performance of windows on my linux build. For example, Test setup: pgbench -i -s 5 Test run: pgbench -T 120 You may think this is a short test, but running it for much longer does not seem to make a significant difference. Now running on windows I get ~ 700 TPS, but on linux I am getting ~70 TPS. The hardware configuration is Dual Opteron, 8GB Ram and 4 sata disks in a Hardware Raid 10 configuration. There is a change in hardware configuration, from Raid5 on the Windows system - But I would expect that switching to raid 10 would give a performance boost. Here are some of the changed settings in my liunx configuration: shared_buffers = 1900MB work_mem = 50MB checkpoint_segments = 32 Am I missing something obvious - or is it a waste of time using the pgbench as a judge of optimisation in these circumstances? Thanks. Howard Cole www.selestial.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] Configuration Optimisation
On Thursday 14. January 2010 14.31.07 Howard Cole wrote: Test setup: pgbench -i -s 5 Test run: pgbench -T 120 You may think this is a short test, but running it for much longer does not seem to make a significant difference. Now running on windows I get ~ 700 TPS, but on linux I am getting ~70 TPS. The hardware configuration is Dual Opteron, 8GB Ram and 4 sata disks in a Hardware Raid 10 configuration. For what it's worth, on my aging Gentoo Linux workstation with a Pentium 4 @ 3 GHz, 1 GB RAM and a single 400 GB SATA Seagate Barracuda disk, default Postgres 8.4.2 installation, I get: l...@balapapa ~ $ pgbench -i -s 5 ... l...@balapapa ~ $ pgbench -T 120 starting vacuum...end. transaction type: TPC-B (sort of) scaling factor: 5 query mode: simple number of clients: 1 duration: 120 s number of transactions actually processed: 29990 tps = 249.694058 (including connections establishing) tps = 249.700935 (excluding connections establishing) 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: R: Re: R: Re: [GENERAL] Weird EXECUTE ... USING behaviour
On Wednesday 13 January 2010 10:19:57 pm Vincenzo Romano wrote: Scott, thanks for that I must have read through that section several times at least with out picking up on it. -- Adrian Klaver adrian.kla...@gmail.com Really? That section is not in any page of the v8.4.2 documentation either PDF or HTML. The sentence has been introduced (yesterday?) in 8.5devel, which is far from being current. http://www.postgresql.org/docs/8.4/interactive/plpgsql-implementation.html 38.10.1. Variable Substitution Last paragraph. I only hope they won't change the manual to match the feature/bug (warning: new joke) So that was not a joke at all! :-( -- 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
Re: R: Re: R: Re: [GENERAL] Weird EXECUTE ... USING behaviour
2010/1/14 Adrian Klaver adrian.kla...@gmail.com: On Wednesday 13 January 2010 10:19:57 pm Vincenzo Romano wrote: Scott, thanks for that I must have read through that section several times at least with out picking up on it. -- Adrian Klaver adrian.kla...@gmail.com Really? That section is not in any page of the v8.4.2 documentation either PDF or HTML. The sentence has been introduced (yesterday?) in 8.5devel, which is far from being current. http://www.postgresql.org/docs/8.4/interactive/plpgsql-implementation.html 38.10.1. Variable Substitution Last paragraph. I only hope they won't change the manual to match the feature/bug (warning: new joke) So that was not a joke at all! :-( -- Adrian Klaver adrian.kla...@gmail.com Now I'm pretty surfe it's a bug. CREATE OR REPLACE FUNCTION f() RETURNS VOID LANGUAGE plpgsql AS $function$ DECLARE cmd TEXT; BEGIN EXECUTE ' SELECT $l0$ALTER TABLE test ALTER COLUMN i SET DEFAULT $1 $l0$ ' INTO cmd USING 42; RAISE INFO '%',cmd; END; $function$ SELECT f(); INFO: ALTER TABLE test ALTER COLUMN i SET DEFAULT $1 The command to be executed is DML (SELECT). The substitution doesn't take place. -- Vincenzo Romano NotOrAnd Information Technologies cel. +39 339 8083886 | gtalk. vincenzo.rom...@notorand.it fix. +39 0823 454163 | skype. notorand.it fax. +39 02 700506964 | msn. notorand.it 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] Configuration Optimisation
On 1/14/2010 7:31 AM, Howard Cole wrote: Hi, I am trying to optimise a database server to give the best performance possible, so I switched from windows 2k3 to linux (ubuntu 9.10) on the basis that most people seem to be of the opinion that postgres runs better on linux than windows. To test my optimisation of the system, I run a simple pgbench setup for 2 minutes. Not very scientific I realise but it has helped me tune systems quite well in the past. My problem is that I am getting nowhere near the performance of windows on my linux build. For example, Test setup: pgbench -i -s 5 Test run: pgbench -T 120 You may think this is a short test, but running it for much longer does not seem to make a significant difference. Now running on windows I get ~ 700 TPS, but on linux I am getting ~70 TPS. The hardware configuration is Dual Opteron, 8GB Ram and 4 sata disks in a Hardware Raid 10 configuration. There is a change in hardware configuration, from Raid5 on the Windows system - But I would expect that switching to raid 10 would give a performance boost. Here are some of the changed settings in my liunx configuration: shared_buffers = 1900MB work_mem = 50MB checkpoint_segments = 32 Am I missing something obvious - or is it a waste of time using the pgbench as a judge of optimisation in these circumstances? Thanks. Howard Cole www.selestial.com I think an important question is: are both os's really flushing all the way to disk, or is someone lying to you? Assuming your workload is IO bound, I'd bet windows is write caching and linux is not. Did you try a dd test on linux? This is a HW Raid, are there any params you can set for caching/writeback/etc? Do you know what the windows settings were? -Andy -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: R: Re: R: Re: [GENERAL] Weird EXECUTE ... USING behaviour
2010/1/14 Vincenzo Romano vincenzo.rom...@notorand.it: 2010/1/14 Adrian Klaver adrian.kla...@gmail.com: On Wednesday 13 January 2010 10:19:57 pm Vincenzo Romano wrote: Scott, thanks for that I must have read through that section several times at least with out picking up on it. -- Adrian Klaver adrian.kla...@gmail.com Really? That section is not in any page of the v8.4.2 documentation either PDF or HTML. The sentence has been introduced (yesterday?) in 8.5devel, which is far from being current. http://www.postgresql.org/docs/8.4/interactive/plpgsql-implementation.html 38.10.1. Variable Substitution Last paragraph. I only hope they won't change the manual to match the feature/bug (warning: new joke) So that was not a joke at all! :-( -- Adrian Klaver adrian.kla...@gmail.com Now I'm pretty surfe it's a bug. CREATE OR REPLACE FUNCTION f() RETURNS VOID LANGUAGE plpgsql AS $function$ DECLARE cmd TEXT; BEGIN EXECUTE ' SELECT $l0$ALTER TABLE test ALTER COLUMN i SET DEFAULT $1 $l0$ ' INTO cmd USING 42; RAISE INFO '%',cmd; END; $function$ SELECT f(); INFO: ALTER TABLE test ALTER COLUMN i SET DEFAULT $1 The command to be executed is DML (SELECT). The substitution doesn't take place. yes. You cannot call SELECT 'ALTER ...' Regards Pavel Stehule -- Vincenzo Romano NotOrAnd Information Technologies cel. +39 339 8083886 | gtalk. vincenzo.rom...@notorand.it fix. +39 0823 454163 | skype. notorand.it fax. +39 02 700506964 | msn. notorand.it 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: R: Re: R: Re: [GENERAL] Weird EXECUTE ... USING behaviour
2010/1/14 Pavel Stehule pavel.steh...@gmail.com: 2010/1/14 Vincenzo Romano vincenzo.rom...@notorand.it: 2010/1/14 Adrian Klaver adrian.kla...@gmail.com: On Wednesday 13 January 2010 10:19:57 pm Vincenzo Romano wrote: ... CREATE OR REPLACE FUNCTION f() RETURNS VOID LANGUAGE plpgsql AS $function$ DECLARE cmd TEXT; BEGIN EXECUTE ' SELECT $l0$ALTER TABLE test ALTER COLUMN i SET DEFAULT $1 $l0$ ' INTO cmd USING 42; RAISE INFO '%',cmd; END; $function$ SELECT f(); INFO: ALTER TABLE test ALTER COLUMN i SET DEFAULT $1 The command to be executed is DML (SELECT). The substitution doesn't take place. yes. You cannot call SELECT 'ALTER ...' SELECT 'ALTER ...' is to select a text string into a variable! You mean the parse will give a look into my constant string to see whether I'm trying to build a dynamic DDL command? This would be awesome! -- 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] Configuration Optimisation
On Thursday 14. January 2010 16.02.12 Andy Colson wrote: I think an important question is: are both os's really flushing all the way to disk, or is someone lying to you? Assuming your workload is IO bound, I'd bet windows is write caching and linux is not. See my reply above. My 250 tps seems in line with the 700 tps on a modern system that the OP gets on his Windows setup. To me it seems like something is broken on his Ubuntu setup. 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: R: Re: R: Re: [GENERAL] Weird EXECUTE ... USING behaviour
2010/1/14 Vincenzo Romano vincenzo.rom...@notorand.it: 2010/1/14 Pavel Stehule pavel.steh...@gmail.com: 2010/1/14 Vincenzo Romano vincenzo.rom...@notorand.it: 2010/1/14 Adrian Klaver adrian.kla...@gmail.com: On Wednesday 13 January 2010 10:19:57 pm Vincenzo Romano wrote: ... CREATE OR REPLACE FUNCTION f() RETURNS VOID LANGUAGE plpgsql AS $function$ DECLARE cmd TEXT; BEGIN EXECUTE ' SELECT $l0$ALTER TABLE test ALTER COLUMN i SET DEFAULT $1 $l0$ ' INTO cmd USING 42; RAISE INFO '%',cmd; END; $function$ SELECT f(); INFO: ALTER TABLE test ALTER COLUMN i SET DEFAULT $1 The command to be executed is DML (SELECT). The substitution doesn't take place. yes. You cannot call SELECT 'ALTER ...' SELECT 'ALTER ...' is to select a text string into a variable! You mean the parse will give a look into my constant string to see whether I'm trying to build a dynamic DDL command? This would be awesome! -- Vincenzo Romano NotOrAnd Information Technologies NON QVIETIS MARIBVS NAVTA PERITVS This instead works: CREATE OR REPLACE FUNCTION public.f() RETURNS void LANGUAGE plpgsql AS $function$ DECLARE cmd1 TEXT; cmd2 TEXT; cmd3 TEXT; BEGIN cmd1 := 'ALTER TABLE test ALTER COLUMN i SET DEFAULT '; EXECUTE 'SELECT $1' INTO cmd2 USING 42; cmd3 := cmd1||cmd2; RAISE INFO '%',cmd3; execute cmd3; END; $function$ The point (in my case) is that the list of expressions (not variables) after the USING is dynamic itself. I can also put 42 into a variable and use it's value after the USING. But this is a lot of extra work just because the values after the USING lexeme are not evaluated by the plpgsql and replaced. It will be the SQL engine itself. -- 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] Configuration Optimisation
Leif Biberg Kristensen wrote: On Thursday 14. January 2010 16.02.12 Andy Colson wrote: See my reply above. My 250 tps seems in line with the 700 tps on a modern system that the OP gets on his Windows setup. To me it seems like something is broken on his Ubuntu setup. regards, I am hoping your assumptions are correct. I have reinstalled ubuntu from scratch as I had been tinkering with the power management on the server. Once the RAID has rebuilt, I shall try again. -- 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] Configuration Optimisation
On Thursday 14. January 2010 16.55.07 Howard Cole wrote: Leif Biberg Kristensen wrote: On Thursday 14. January 2010 16.02.12 Andy Colson wrote: See my reply above. My 250 tps seems in line with the 700 tps on a modern system that the OP gets on his Windows setup. To me it seems like something is broken on his Ubuntu setup. regards, I am hoping your assumptions are correct. I have reinstalled ubuntu from scratch as I had been tinkering with the power management on the server. Once the RAID has rebuilt, I shall try again. What kind of file system are you running? 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] Configuration Optimisation
Leif Biberg Kristensen wrote: On Thursday 14. January 2010 16.55.07 Howard Cole wrote: Leif Biberg Kristensen wrote: On Thursday 14. January 2010 16.02.12 Andy Colson wrote: See my reply above. My 250 tps seems in line with the 700 tps on a modern system that the OP gets on his Windows setup. To me it seems like something is broken on his Ubuntu setup. regards, I am hoping your assumptions are correct. I have reinstalled ubuntu from scratch as I had been tinkering with the power management on the server. Once the RAID has rebuilt, I shall try again. What kind of file system are you running? regards, The file system is ext4. If I turn fsync off I get a TPS of 1120 so I think there is an issue with the speed of the array. The dd test give a speed of 125MB/s which seems reasonable to me. I can leave fsync off but I suspect that I am just ignoring the problem. Howard. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: R: Re: R: Re: [GENERAL] Weird EXECUTE ... USING behaviour
2010/1/14 Vincenzo Romano vincenzo.rom...@notorand.it: 2010/1/14 Pavel Stehule pavel.steh...@gmail.com: 2010/1/14 Vincenzo Romano vincenzo.rom...@notorand.it: 2010/1/14 Adrian Klaver adrian.kla...@gmail.com: On Wednesday 13 January 2010 10:19:57 pm Vincenzo Romano wrote: ... CREATE OR REPLACE FUNCTION f() RETURNS VOID LANGUAGE plpgsql AS $function$ DECLARE cmd TEXT; BEGIN EXECUTE ' SELECT $l0$ALTER TABLE test ALTER COLUMN i SET DEFAULT $1 $l0$ ' INTO cmd USING 42; RAISE INFO '%',cmd; END; $function$ SELECT f(); INFO: ALTER TABLE test ALTER COLUMN i SET DEFAULT $1 The command to be executed is DML (SELECT). The substitution doesn't take place. yes. You cannot call SELECT 'ALTER ...' SELECT 'ALTER ...' is to select a text string into a variable! You mean the parse will give a look into my constant string to see whether I'm trying to build a dynamic DDL command? This would be awesome! sorry. This is too much complicate. Why do you use SELECT? just EXECUTE 'ALTER ... SET DEFAULT ' || 42. There is other argument against USING + DDL. ALTER clause has syntax: ALTER TABLE x SET DEFAULT expr. but with USING clause you can pass only a value 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
[GENERAL] PostreSQL PostgreSQL 8.1.18 and Tsearch2 context error
Hi guys, I'm working with full-text search on my project, however i'm receiving an Config file error: 7 ERROR: no tsearch config CONTEXT message but i don't know what it means. Can you help me about this error?, i will apreciate so much any suggestion about it. Thank's in advance, Rodrigo
Re: [GENERAL] FOSDEM dinner
On Thu, Jan 14, 2010 at 09:51:34AM +0200, Dave Coventry wrote: Yes, I'm in South Africa, which might make it problematic! You're on the right land mass, assuming you count being able to cross the Suez canal on foot ;) Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Moving database cluster
An Ubuntu install creates a postgres cluster automatically on /var/lib/postgresql/8.4/main Whats the best procedure for moving this cluster to an other location? Should I just rerun initdb? What happens then with the default cluster or how could I delete it? Thanks, Fernando. -- 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] Moving database cluster
Le 14/01/2010 21:40, Fernando Hevia a écrit : An Ubuntu install creates a postgres cluster automatically on /var/lib/postgresql/8.4/main Whats the best procedure for moving this cluster to an other location? Should I just rerun initdb? What happens then with the default cluster or how could I delete it? If you don't have any data on it, the best way is to drop it with pg_dropcluster, and create a new one with pg_createcluster. -- Guillaume. http://www.postgresqlfr.org 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
Re: [GENERAL] Moving database cluster
On Thursday 14 January 2010, Fernando Hevia elucidated thus: An Ubuntu install creates a postgres cluster automatically on /var/lib/postgresql/8.4/main Whats the best procedure for moving this cluster to an other location? Should I just rerun initdb? What happens then with the default cluster or how could I delete it? The easiest way is to shut down Pg, move the 'main' directory somewhere else, and then point a symlink to the new location. j -- Joshua Kugler Part-Time System Admin/Programmer http://www.eeinternet.com PGP Key: http://pgp.mit.edu/ ID 0x14EA086E -- 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] Moving database cluster
-Mensaje original- De: Guillaume Lelarge [mailto:guilla...@lelarge.info] Le 14/01/2010 21:40, Fernando Hevia a écrit : An Ubuntu install creates a postgres cluster automatically on /var/lib/postgresql/8.4/main Whats the best procedure for moving this cluster to an other location? Should I just rerun initdb? What happens then with the default cluster or how could I delete it? If you don't have any data on it, the best way is to drop it with pg_dropcluster, and create a new one with pg_createcluster. Sound advice. Thanks!! -- 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] Moving database cluster
-Mensaje original- De: On Thursday 14 January 2010, Fernando Hevia elucidated thus: An Ubuntu install creates a postgres cluster automatically on /var/lib/postgresql/8.4/main Whats the best procedure for moving this cluster to an other location? Should I just rerun initdb? What happens then with the default cluster or how could I delete it? The easiest way is to shut down Pg, move the 'main' directory somewhere else, and then point a symlink to the new location. Thanks for your reply. I had considered this first but then I wasn't sure if there would be any performance penalty. The current main directory sits on a 'slow' RAID 1 volume while the new one will sit on a 'fast' 12 disk RAID 10 volume. I guess the symlink shouldn't be troublesome but I don't know if some PG process will continually be reading this link encumbering somehow the RAID 1 disks. Regards, Fernando. -- 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] Moving database cluster
On Thursday 14 January 2010, Fernando Hevia elucidated thus: The easiest way is to shut down Pg, move the 'main' directory somewhere else, and then point a symlink to the new location. Thanks for your reply. I had considered this first but then I wasn't sure if there would be any performance penalty. The current main directory sits on a 'slow' RAID 1 volume while the new one will sit on a 'fast' 12 disk RAID 10 volume. I guess the symlink shouldn't be troublesome but I don't know if some PG process will continually be reading this link encumbering somehow the RAID 1 disks. I'm not sure how Pg does it, but I remember reading several years ago that MySQL would, upon startup, resolve the symlink, and use the real path name for the rest of its execution. I would assume Pg does something similar. j -- Joshua Kugler Part-Time System Admin/Programmer http://www.eeinternet.com PGP Key: http://pgp.mit.edu/ ID 0x14EA086E -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Avoid transaction abot if/when constraint violated
Hello ! I have a long list of records I want to insert into a table in such a way as I can trap and report any/all constraint violations before rolling back (or opting to commit). Unfortunately, after I hit the first constraint violation, it aborts the transaction, and then reports ERROR: current transaction is aborted, commands ignored until end of transaction block. Is there a way around this? Thanks in Advance!
Re: [GENERAL] Avoid transaction abot if/when constraint violated
On Thu, 2010-01-14 at 15:12 -0700, Gauthier, Dave wrote: Hello ! I have a long list of records I want to insert into a table in such a way as I can trap and report any/all constraint violations before rolling back (or opting to commit). Unfortunately, after I hit the first constraint violation, it aborts the transaction, and then reports “ERROR: current transaction is aborted, commands ignored until end of transaction block”. Is there a way around this? Only if it is a foreign key issue in which case you can defer the check. If it is a single transaction, and you insert a bad record the whole transaction fails. Joshua D. Drake Thanks in Advance! -- PostgreSQL.org Major Contributor Command Prompt, Inc: http://www.commandprompt.com/ - 503.667.4564 Consulting, Training, Support, Custom Development, Engineering Respect is earned, not gained through arbitrary and repetitive use or Mr. or Sir. -- 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] Avoid transaction abot if/when constraint violated
Ya, I don't mind that it eventually fails (why have constraints otherwise), but I'd like to see all the constraint violations for the set of records. I actually have something working. I'm coding in perl/DBI, and I just rollback after each constraint violation and keep going. Nothing from the entire stream is committed until/unless they're all clean. Thanks -Original Message- From: Joshua D. Drake [mailto:j...@commandprompt.com] Sent: Thursday, January 14, 2010 6:35 PM To: Gauthier, Dave Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Avoid transaction abot if/when constraint violated On Thu, 2010-01-14 at 15:12 -0700, Gauthier, Dave wrote: Hello ! I have a long list of records I want to insert into a table in such a way as I can trap and report any/all constraint violations before rolling back (or opting to commit). Unfortunately, after I hit the first constraint violation, it aborts the transaction, and then reports ERROR: current transaction is aborted, commands ignored until end of transaction block. Is there a way around this? Only if it is a foreign key issue in which case you can defer the check. If it is a single transaction, and you insert a bad record the whole transaction fails. Joshua D. Drake Thanks in Advance! -- PostgreSQL.org Major Contributor Command Prompt, Inc: http://www.commandprompt.com/ - 503.667.4564 Consulting, Training, Support, Custom Development, Engineering Respect is earned, not gained through arbitrary and repetitive use or Mr. or Sir. -- 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] Avoid transaction abot if/when constraint violated
On Thu, Jan 14, 2010 at 3:12 PM, Gauthier, Dave dave.gauth...@intel.com wrote: Hello ! I have a long list of records I want to insert into a table in such a way as I can trap and report any/all constraint violations before rolling back (or opting to commit). Unfortunately, after I hit the first constraint violation, it aborts the transaction, and then reports “ERROR: current transaction is aborted, commands ignored until end of transaction block”. You're probably thinking in terms of how other databases work. For many dbs, an aborted transaction can have a very high cost (usually in terms of rollback) so there was a lot of work put into allowing you to work around these errors and so on. In Pgsql an aborted transaction has a fairly low cost. Insert 10,000 rows, change your mind, roll is back, and you're done, no waiting for the rollback to happen, it's immediate. Because of this, the transactional semantics in pgsql are very simple. Begin a transaction, get an error, abort. They've been made a bit more robust with the introduction of save points, which allow you to roll a transaction back to the latest savepoint and start again from there without losing all the work from before that savepoint. However, savepoints aren't free, or even necessarily cheap. Setting one and releasing it before each statement makes your overall transaction quite slow. If you're trying to massage data to get it into a format that will insert into a table, a preferred method for me is to put it into a load table, then check to see if the rows there pass, and if they don't delete or change them to fit. -- 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] Moving database cluster
On Thu, Jan 14, 2010 at 06:21:14PM -0300, Fernando Hevia wrote: - - - -Mensaje original- - De: Guillaume Lelarge [mailto:guilla...@lelarge.info] - - Le 14/01/2010 21:40, Fernando Hevia a écrit : - - An Ubuntu install creates a postgres cluster automatically on - /var/lib/postgresql/8.4/main Whats the best procedure for - moving this - cluster to an other location? - Should I just rerun initdb? What happens then with the - default cluster - or how could I delete it? - - - If you don't have any data on it, the best way is to drop it - with pg_dropcluster, and create a new one with pg_createcluster. - - - - Sound advice. Thanks!! in SLES, assuming you have no data in the DB, I'd go to /etc/sysconfig/postgresql modify POSTGRES_DATADIR=/my/new/path and then (logout/log back in to get your PGDATA set correctly and ) initdb No need to mess with symlinks. I'm sure ubuntu has something similar to sysconfig 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] Split pg_dump dump into files and then combine it back
Hello. Is there a tool (or a way) to parse a pg_dump'ed (structure only) dump file into smaller files (each function in its own file, its table in its own etc.) with ability to combine these files later into the proper dump file? The main problem is dependencies. Sometimes functions are defined before types, sometimes after. The same for cross-schema references. Dump parsing is simple, but this process drops dependencies between objects and could not be reversed easily.
Re: [GENERAL] Avoid transaction abot if/when constraint violated
Gauthier, Dave wrote: Hello ! I have a long list of records I want to insert into a table in such a way as I can trap and report any/all constraint violations before rolling back (or opting to commit). Unfortunately, after I hit the first constraint violation, it aborts the transaction, and then reports “ERROR: current transaction is aborted, commands ignored until end of transaction block”. Is there a way around this? use savepoints inside the transaction for each insert. your app will have to figure out how to track the errors if it wants to postpone any rollback/commit decision til the end. -- 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] Split pg_dump dump into files and then combine it back
Dmitry Koterov dmi...@koterov.ru writes: Is there a tool (or a way) to parse a pg_dump'ed (structure only) dump file into smaller files (each function in its own file, its table in its own etc.) with ability to combine these files later into the proper dump file? The main problem is dependencies. Sometimes functions are defined before types, sometimes after. The same for cross-schema references. Dump parsing is simple, but this process drops dependencies between objects and could not be reversed easily. I don't know of any existing tool. However, pg_dump can be told to print out its internal dependency information, so in principle you could build one that looks at that info and reconstitutes the chunks in a safe order. I'm not sure I see the point though ... 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] Memory Access Violation While Using PQexec
On 14/01/2010 4:49 PM, Yan Cheng Cheok wrote: I encounter case when I call a stored procedure for 299166 th times (intensive, i put a non-stop while true loop to call stored procedure) , the following exception will be thrown from PQexec. I am rather sure the exception are from PQexec, as there is a just before cout and just after cout wrap around PQexec. std::cout Start PQexec sql std::endl; PGresult *res = PQexec(connection, sql.c_str()); std::cout End PQexec sql std::endl; const bool status = (PQresultStatus(res) == PGRES_TUPLES_OK); PQclear(res); End PQexec... is not being printed. The call stack is : libpq.dll!10017ea5() [Frames below may be incorrect and/or missing, no symbols loaded for libpq.dll] You need to add c:\Program Files\PostgreSQL\8.4\symbols to your debug symbol path. See: http://wiki.postgresql.org/wiki/Getting_a_stack_trace_of_a_running_PostgreSQL_backend_on_Windows That will also help Visual Studio break accurately, which it probably isn't doing right now. There's a fair chance that where you broke in the debugger has nothing to do with what is actually wrong. Any suggestion? I'm too clueless to give you a useful answer here, beyond telling you to set your debug environment up properly and try again. If you can extract this into a standalone test case (a single compilable C or C++ program, plus a .sql file) that'd be rather handy, though. It also means that if it's a bug in your code - say, you're corrupting the heap - then building a test case will help you track that down by elimination. -- 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
[GENERAL] DELETE Weirdness
There are two ways of deleting things, DELETE FROM table WHERE PK IN ( ...SET... ); DELETE FROM table USING table2 WHERE join condition AND filter clause; I am deleting from a table where the rows are inter-related (it is a tree); using the first version with IN, it does not delete all rows; there is a trigger that rearranges the rows when a row is deleted (updating references etc.). Disabling this trigger results in correct behavoir. However, using the second version (using USING) works as expected with or without disabling the said triggers. I cannot quite understand this; Are the contents of the IN query worked out ONCE per outer query and therefore become invalid when DELETE comes along and changes items that were part of the set ? (for e.g.) -ravi -- :wq -- 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] DELETE Weirdness
On Fri, 2010-01-15 at 13:55 +1300, Ravi Chemudugunta wrote: I cannot quite understand this; Are the contents of the IN query worked out ONCE per outer query and therefore become invalid when DELETE comes along and changes items that were part of the set ? (for e.g.) The command itself gets one snapshot that sees tuples as they are just before the command begins executing. That snapshot is used for the predicate (WHERE clause) throughout the execution, so it will remain static. The triggered functions may execute commands that get their own snapshots and see new tuples, but the outer command won't see those. You can see this with an experiment: create table mytable (i int); insert into mytable values(1); create or replace function mytrfn() returns trigger as $$ begin update mytable set i=-i; return new; end; $$ language plpgsql; create trigger mytr before insert on mytable for each row execute procedure mytrfn(); insert into mytable select i from mytable; select * from mytable; i -1 1 (2 rows) If the select i from mytable on the right side of the insert was constantly being re-evaluated, there would be two -1 values in mytable. Regards, Jeff Davis -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Calling a plpgsql function with composite type as parameter?
I'm working on an e-commerce site that calls various plpgsql functions
from a Python app. One of the things I need to do is create a
shopping cart and add several items to it. I'd like for both of these
steps to be contained within the same transaction so if an error
occurs adding an item to the cart, the entire cart creation is rolled
back. I'm attempting to use something like the code below (I've
simplified it). However, a) I'm not sure if this is the correct
architectural decision and b) I haven't been able to figure how how to
call this function using a composite type (my _cart_contents) as a
parameter. I'd greatly appreciate any suggestions. Thanks!
CREATE TABLE carts (id serial, cart_owner varchar, cart_name varchar);
CREATE TABLE carts_items (id serial, cart_id int REFERENCES carts(id),
product_name varchar, price decimal(5,2) );
CREATE TYPE cart_item_type AS (product_name varchar, price decimal(5,2));
CREATE OR REPLACE FUNCTION cart_create(
_user_id int
,_cart_name varchar
,_cart_contents cart_item_type[]
) RETURNS bool AS $$
DECLARE
_cart_id int;
_id int;
_i int;
_n varchar;
_p decimal(5,2);
_product_id int;
BEGIN
INSERT INTO carts (cart_owner, cart_name)
VALUES (_user_id, _cart_name);
SELECT id INTO _cart_id FROM carts WHERE id =
CURRVAL('carts_id_seq');
FOR _i IN COALESCE(array_lower(_cart_contents,1),0) ..
COALESCE(array_upper(_cart_contents,1),-1) LOOP
_n := _cart_contents[_i]['product_name'];
_p := _cart_contents[_i]['price'];
INSERT INTO cart_items (cart_id, product_name, price)
VALUES (_cart_id, _n, _p);
END LOOP;
RETURN True;
END; $$ LANGUAGE plpgsql;
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] vacuum issues under load?
We have recently discovered a problem with our slony-1 cluster of 8.1.19 installs. Specifically, we are unable to vacuum a table on the master node; vacuum always hangs on the same index of the same table. If we do a slony switchover and make the other node the master, then *it* will become unable to vacuum that index. Vacuum on the slave always works quickly and without issue. Vacuum does not hang anywhere else. When we tried to strace the vacuuming backend, it appeared as if it was trying to acquire a lock, but pg_lock showed nothing unexpected for that index. We can also manually acquire an access exclusive lock on the offending table if we desire. FWIW, we have about 1,000 sessions, and while most of them are idle, we still average a couple hundred queries/s. The index in question is a simple unique btree over a column of type character(40). Has anybody else experienced anything like this? We are hoping this problem magically goes away when we upgrade to 8.4 next month, but it would be great if we could solve it before then. Thanks for any suggestions -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: R: Re: R: Re: [GENERAL] Weird EXECUTE ... USING behaviour
2010/1/14 Pavel Stehule pavel.steh...@gmail.com: 2010/1/14 Vincenzo Romano vincenzo.rom...@notorand.it: 2010/1/14 Pavel Stehule pavel.steh...@gmail.com: 2010/1/14 Vincenzo Romano vincenzo.rom...@notorand.it: 2010/1/14 Adrian Klaver adrian.kla...@gmail.com: On Wednesday 13 January 2010 10:19:57 pm Vincenzo Romano wrote: ... CREATE OR REPLACE FUNCTION f() RETURNS VOID LANGUAGE plpgsql AS $function$ DECLARE cmd TEXT; BEGIN EXECUTE ' SELECT $l0$ALTER TABLE test ALTER COLUMN i SET DEFAULT $1 $l0$ ' INTO cmd USING 42; RAISE INFO '%',cmd; END; $function$ SELECT f(); INFO: ALTER TABLE test ALTER COLUMN i SET DEFAULT $1 The command to be executed is DML (SELECT). The substitution doesn't take place. yes. You cannot call SELECT 'ALTER ...' SELECT 'ALTER ...' is to select a text string into a variable! You mean the parse will give a look into my constant string to see whether I'm trying to build a dynamic DDL command? This would be awesome! sorry. This is too much complicate. Why do you use SELECT? just EXECUTE 'ALTER ... SET DEFAULT ' || 42. There is other argument against USING + DDL. ALTER clause has syntax: ALTER TABLE x SET DEFAULT expr. but with USING clause you can pass only a value Pavel -- Vincenzo Romano NotOrAnd Information Technologies NON QVIETIS MARIBVS NAVTA PERITVS This is a fairly complete background of my issue. At the very base I'm talking about a two level dynamic SQL (I have a function which creates functions that compose and run the dynamic SQL), thus the usage of $l0$ (higher level) and $l2$ (lower level) quoting. In particular, mt (master table), co (constraint) and va (variable arguments) are text strings at the higher level and will become SQL in the lower level. Those data are retrieved from configuration tables and are no way static. While ct (child table) is unknown at the higher level but is defined at the lower level as a text string to become SQL at the EXECUTE...USING level. 1. I need to create at run-time a number of child tables (http://www.postgresql.org/docs/8.4/static/ddl-inherit.html). I can easily do this with something like (there's no need for the USING clause): execute $l2$ create table $l2$||ct||$l2$ ( like $l0$||mt||$l0$ including defaults including constraints including indexes ) $l2$; 2. Then I need to fill those tables up from the master: execute $l2$ insert into $l2$||ct||$l2$ select * from only $l0$||mt||$l0$ where $l0$||co||$l0$ $l2$ using $l0$||va||$l0$; here co (constraint) and va (variable arguments) are text variables taken from configuration tables. What I have is something like: co := 'recorddate=$1 and recorddate$2 and afield=$3' va := 'rec.d0,rec.d1,rec.afield' This also works as it is DML and I expect (and can actually see) the $1,$2 and $3 *values* replaced into the string *before* it is sent to the execution. 3. Then I need to add the TABLE-level CHECK condition in order to exploit the constraint_exclusion = on: execute $l2$ alter table $l2$||ct||$l2$ add check( $l0$||co||$l0$ ) $l2$ using $l0$||va||$l0$; This doesn't work for a number of reasons. As you can see, there no easy way to replace the actual values taken accordingly to va into the template co but using the USING clause. This is the real and central knot in my problem. So I tried to implement step #3 into a two pass process, one to just expand the placeholders, one to execute a completely static DDL command. Something like: execute $l2$ select '$l0$||co||$l0$' $l2$ into textvar using $l0$||va||$l0$; execute $l2$ alter table $l2$||ct||$l2$ add check( $l2$||textvar||$l2$ ) $l2$; This also doesn't work as: execute $l2$select '$1'$l2$ into textvar using 'hello'; will not expand the $1 placeholder probably because it is within quotes. If I skip the quotes, the select won't be able to find variables mentioned into co. So, this is why I used SELECT. Unless there's a better (and working) advise. -- 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
