Re: [GENERAL] pl/pgSQL variable substitution
On 17 Feb 2011, at 5:33, Jeremy Palmer wrote: > Hi, > > I'm creating a pl/pgSQL function that returns a table that has a column name > which is the same as a PostgreSQL reserved. In the below example a have > returning table with a column called 'desc': > > CREATE OR REPLACE FUNCTION bad_func() RETURNS TABLE (bar INTEGER, "desc" > VARCHAR(100)) AS $$ > BEGIN >RETURN QUERY >SELECT foo.bar, foo."desc" >FROM foo >ORDER BY foo."desc" DESC; > END; > $$ LANGUAGE plpgsql; > > When I have a query that uses DESC reserved word within the function the > following variable substitution occurs: > > ERROR: syntax error at or near "$1" > LINE 1: SELECT foo.bar, foo."desc" FROM foo."desc" ORDER BY foo."desc" > $1 > ^ > > In my case I really would like to keep the table names i.e. no '_' etc. Your problem isn't with your table names, but with your parameter names. The "desc" parameter from your function declaration matches the DESC keyword in your query, from the looks of it. Either use the old unnamed function declaration and use $1 and $2 in your function, or rename your parameters to something that won't be in your queries as something else than a parameter reference. That said, I don't see where in your function you intend to use those parameters. Possibly you're trying to create a dynamic query? Pavel answered that part of your question already. Alban Hertroys -- Screwing up is an excellent way to attach something to the ceiling. !DSPAM:737,4d5ccf3211731594261662! -- 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] pl/pgSQL variable substitution
Hello
you cannot use a variable as column name or table name. It's not
possible, because it can change execution plan and it isn't allowed.
Use a dynamic SQL instead.
RETURN QUERY EXECUTE 'SELECT foo.bar, foo.' || quote_ident("desc") ||
' FROM foo ORDER BY foo.' || quote_ident("desc") || ' DESC'
Regards
Pavel Stehule
2011/2/17 Jeremy Palmer :
> Hi,
>
> I'm creating a pl/pgSQL function that returns a table that has a column name
> which is the same as a PostgreSQL reserved. In the below example a have
> returning table with a column called 'desc':
>
> CREATE OR REPLACE FUNCTION bad_func() RETURNS TABLE (bar INTEGER, "desc"
> VARCHAR(100)) AS $$
> BEGIN
> RETURN QUERY
> SELECT foo.bar, foo."desc"
> FROM foo
> ORDER BY foo."desc" DESC;
> END;
> $$ LANGUAGE plpgsql;
>
> When I have a query that uses DESC reserved word within the function the
> following variable substitution occurs:
>
> ERROR: syntax error at or near "$1"
> LINE 1: SELECT foo.bar, foo."desc" FROM foo."desc" ORDER BY foo."desc"
> $1
> ^
>
> In my case I really would like to keep the table names i.e. no '_' etc.
>
> I can think of a few options to do this with varying levels of syntactic
> sugar:
>
> 1) Use RETURNS SETOF RECORD instead of RETURNS TABLE:
>
> CREATE OR REPLACE FUNCTION test1() RETURNS SETOF RECORD AS $$
> BEGIN
> RETURN QUERY
> SELECT
> audit_id,
> "desc"
> FROM crs_sys_code
> ORDER BY "desc" DESC ;
> END;
> $$ LANGUAGE plpgsql;
>
> Not a great interface because you have to declare the return record column
> names and types i.e.:
>
> SELECT * FROM test1() AS (id INTEGER, "desc" TEXT);
>
> 2) Create a composite type for the table row and use RETURNS SETOF:
>
> CREATE OR REPLACE FUNCTION test2() RETURNS SETOF table_type AS $$
> BEGIN
> RETURN QUERY
> SELECT
> audit_id,
> "desc"
> FROM crs_sys_code
> ORDER BY "desc" DESC;
> END;
> $$ LANGUAGE plpgsql;
>
> Better, but you have to create a type specifically for the function.
>
> 3) CREATE a SQL language wrapper around the example in 1):
>
> CREATE OR REPLACE FUNCTION test3() RETURNS TABLE (id INTEGER, "desc" TEXT) AS
> $$
> SELECT * FROM test2() AS (id INTEGER, "desc" TEXT);
> $$ LANGUAGE sql;
>
> Nice interface, but now I need to manage two functions...
>
>
> Does anyone have any other suggestions here?
>
> Is this pl/pgSQL variable substitution seen as a feature of PostgreSQL, or a
> hangover from when PostgreSQL didn't support named function parameters?
> Really drives me crazy when naming variables in pl/pgSQL!
>
> Best Regards,
> Jeremy
> __
>
> This message contains information, which is confidential and may be subject
> to legal privilege.
> If you are not the intended recipient, you must not peruse, use, disseminate,
> distribute or copy this message.
> If you have received this message in error, please notify us immediately
> (Phone 0800 665 463 or i...@linz.govt.nz) and destroy the original message.
> LINZ accepts no responsibility for changes to this email, or for any
> attachments, after its transmission from LINZ.
>
> Thank you.
> __
>
> --
> 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
[GENERAL] pl/pgSQL variable substitution
Hi, I'm creating a pl/pgSQL function that returns a table that has a column name which is the same as a PostgreSQL reserved. In the below example a have returning table with a column called 'desc': CREATE OR REPLACE FUNCTION bad_func() RETURNS TABLE (bar INTEGER, "desc" VARCHAR(100)) AS $$ BEGIN RETURN QUERY SELECT foo.bar, foo."desc" FROM foo ORDER BY foo."desc" DESC; END; $$ LANGUAGE plpgsql; When I have a query that uses DESC reserved word within the function the following variable substitution occurs: ERROR: syntax error at or near "$1" LINE 1: SELECT foo.bar, foo."desc" FROM foo."desc" ORDER BY foo."desc" $1 ^ In my case I really would like to keep the table names i.e. no '_' etc. I can think of a few options to do this with varying levels of syntactic sugar: 1) Use RETURNS SETOF RECORD instead of RETURNS TABLE: CREATE OR REPLACE FUNCTION test1() RETURNS SETOF RECORD AS $$ BEGIN RETURN QUERY SELECT audit_id, "desc" FROM crs_sys_code ORDER BY "desc" DESC ; END; $$ LANGUAGE plpgsql; Not a great interface because you have to declare the return record column names and types i.e.: SELECT * FROM test1() AS (id INTEGER, "desc" TEXT); 2) Create a composite type for the table row and use RETURNS SETOF: CREATE OR REPLACE FUNCTION test2() RETURNS SETOF table_type AS $$ BEGIN RETURN QUERY SELECT audit_id, "desc" FROM crs_sys_code ORDER BY "desc" DESC; END; $$ LANGUAGE plpgsql; Better, but you have to create a type specifically for the function. 3) CREATE a SQL language wrapper around the example in 1): CREATE OR REPLACE FUNCTION test3() RETURNS TABLE (id INTEGER, "desc" TEXT) AS $$ SELECT * FROM test2() AS (id INTEGER, "desc" TEXT); $$ LANGUAGE sql; Nice interface, but now I need to manage two functions... Does anyone have any other suggestions here? Is this pl/pgSQL variable substitution seen as a feature of PostgreSQL, or a hangover from when PostgreSQL didn't support named function parameters? Really drives me crazy when naming variables in pl/pgSQL! Best Regards, Jeremy __ This message contains information, which is confidential and may be subject to legal privilege. If you are not the intended recipient, you must not peruse, use, disseminate, distribute or copy this message. If you have received this message in error, please notify us immediately (Phone 0800 665 463 or i...@linz.govt.nz) and destroy the original message. LINZ accepts no responsibility for changes to this email, or for any attachments, after its transmission from LINZ. Thank you. __ -- 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] Raid Controller Write Cache setting for WAL and Data
Piotr Gasidło wrote: I _can_ afford of loosing some data in case of power failure. But I'm afraid of having database in unrecoverable state after crash. Then turn off synchronous_commit. That's exactly the behavior you get when it's disabled: some data loss after a crash, no risk of database corruption, and faster performance without needing a controller with a battery. If you've already got a RAID controller that accepts a battery, it would be silly not to then buy one though. The controller is normally 75% of the price of the combination, so getting that but not the final piece to really make it perform well wouldn't be a good move. -- Greg Smith 2ndQuadrant USg...@2ndquadrant.com Baltimore, MD PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us "PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books -- 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] question regarding full_page_write
AI Rumman wrote: I can't clearly understand what FULL_PAGE_WRITE parameter is stand for. Documentation suggest that If I make it OFF, then I have the chance for DB crash. Can anyone please tell me how it could be happened? The database writes to disk in 8K blocks. If you can be sure that your disk drives and operating system will always write in 8K blocks, you can get a performance improvement from turning full_page_writes off. But if you do that, and it turns out that when the power is interrupted your disk setup will actually do partial writes of less than 8K, your database can get corrupted. Your system needs to ensure that when a write happens, either the whole thing goes to disk, or none of it does. -- Greg Smith 2ndQuadrant USg...@2ndquadrant.com Baltimore, MD PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us "PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books -- 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] why does the toast table exist?
On Wed, Feb 16, 2011 at 02:36:03PM +0600, AI Rumman wrote: > I have no idea why the TOAST table exists for audit_trial table. > > \d audit_trial > Table "public.audit_trial" >Column |Type | Modifiers > +-+--- > auditid| integer | not null > userid | integer | > module | character varying(255) | > action | character varying(255) | > recordid | character varying(20) | > actiondate | timestamp without time zone | Supposing your encoding is UTF8, each character can consume up to four bytes. A row could use up to around 4 + 4 + 4*(255+255+20) + 8 bytes, which is large enough to be toastable at the default block size. -- 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] Query sought with windowing function to weed out dense points
Hi, On Thu, Feb 17, 2011 at 12:14:28AM +0100, Stefan Keller wrote: > SELECT ST_AsText(geometry), name as label > FROM > peaks t1 > WHERE > t1.id = ( > SELECT id > FROM ( > SELECT ST_SnapToGrid(geometry, 5) as geometry, elevation, id > FROM peaks > ) t2 > WHERE ST_Equals(ST_SnapToGrid(t1.geometry, 5), t2.geometry) > ORDER BY elevation DESC > LIMIT 1 > ) > I think there could be perhaps an even more elegant solution with the > new windowing functions! My idea is to partition peaks around a grid > and chose the one with max(elevation). You might eliminate the correlated subquery, like in: SELECT * FROM ( SELECT ST_AsText(geometry) , name as label , rank() OVER ( PARTITION BY ST_Equals(ST_SnapToGrid(geometry, 5) ORDER BY elevation DESC) FROM peaks ) x WHERE rank = 1; -- query not tested as I don't have postgis available which "feels" more elegant; but you still need a subquery, as window functions are not allowed in the WHERE clause. Cheers, Dr. Gianni Ciolli - 2ndQuadrant Italia PostgreSQL Training, Services and Support gianni.cio...@2ndquadrant.it | www.2ndquadrant.it -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Query sought with windowing function to weed out dense points
Hi, Given a table 'peaks' with the fields id, name, elevation and geometry I'd like to get a query which returns only peaks which dont overlap - and from those which would do, I'd like to get the topmost one (given a certain 'density parameter'). This problem is motivated by a visualization task where label names should'nt overlap because of lack of visual space - as well as because of limited network capacity between db and client! Here is a solution I fiddled about which is based on a grid (ST_SnapToGrid): SELECT ST_AsText(geometry), name as label FROM peaks t1 WHERE t1.id = ( SELECT id FROM ( SELECT ST_SnapToGrid(geometry, 5) as geometry, elevation, id FROM peaks ) t2 WHERE ST_Equals(ST_SnapToGrid(t1.geometry, 5), t2.geometry) ORDER BY elevation DESC LIMIT 1 ) ...where 5 is the 'density parameter': a higher number means larger grid which returns less peaks. This parameter could be parametrised in a stored procedure and set according to the map units. I think there could be perhaps an even more elegant solution with the new windowing functions! My idea is to partition peaks around a grid and chose the one with max(elevation). => Any windowing function gurus around? Yours, S. P.S. I had difficulties finding (OLTP) examples for windowing functions (and unfortunately the slides from PGDay.EU last year aren't available :-<) -- 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] Hide db name and user name in process list arguments
Thomas Kellerer writes: > Gavrina, Irina, 16.02.2011 15:50: >> Is there any way to hide dbname and user name in displayed arguments of >> client connections? > I think that's what the configuration property update_process_title is for. No, that's just meant to suppress the overhead of updating the title for each command. It doesn't prevent backends from advertising their dbname and username once at startup. I think you'd have to modify the source code to do that. 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] Hide db name and user name in process list arguments
Gavrina, Irina, 16.02.2011 15:50: Hi, On Unix systems Postgres process list can beaccessible through‘ps’ utility: ps auxww | grep ^postgres $ ps auxww | grep ^postgres postgres 9600.01.16104 1480 pts/1SN 13:17 0:00 postmaster -i postgres 9630.01.17084 1472 pts/1SN 13:17 0:00 postgres: stats buffer process postgres 9650.01.16152 1512 pts/1SN 13:17 0:00 postgres: stats collector process postgres 9980.02.36532 2992 pts/1SN 13:18 0:00 postgres: tgl runbug 127.0.0.1 idle postgres10030.02.46532 3128 pts/1SN 13:19 0:00 postgres: tgl regression [local] SELECT waiting postgres10160.12.46532 3080 pts/1SN 13:19 0:00 postgres: tgl regression [local] idle in transaction And each client connection has its command line which displays in form: postgres: /user//database//host//activity/ Is there any way to hide dbname and user name in displayed arguments of client connections? I think that's what the configuration property update_process_title is for. http://www.postgresql.org/docs/current/static/runtime-config-statistics.html#GUC-UPDATE-PROCESS-TITLE Regards 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] Recovery with WAL
Albert wrote: > > > Where can I find information about recovery_target_time or > recovery_target_xid. I have two servers db1 and db2, > WAL files are copied from db1 to db2. Database will colapse at 17:10 and > i wan't to recove base from 17:05, so where can I find info about > recovery_time. If you use no recovery time or xid, it will go as far as it can. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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 - recovery.conf
For@ll wrote: > Hi, > > In file recovery.conf I can define recovery_target_time or > recovery_target_xid. > I have question where I cand found this information? Well, the time is wall clock time. It is hard to know the xid to use for recovery. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] PGDay LA @ SCALE 9X : 1 week away
Here's the reminder that PGDay is just one week away. It scheduled for Friday February 25th, 2011 and is hosted by SCALE. For more information: http://sites.google.com/site/pgdayla/home To Register: https://www.socallinuxexpo.org/reg7/ Also, we're look for more volunteers to attend the PostgreSQL booth as the Expo for Saturday and Sunday - February 25th through 26th. Send me an email if your interested. -- Regards, Richard Broersma Jr.
[GENERAL] Hide db name and user name in process list arguments
Hi, On Unix systems Postgres process list can be accessible through 'ps' utility: ps auxww | grep ^postgres $ ps auxww | grep ^postgres postgres 960 0.0 1.1 6104 1480 pts/1SN 13:17 0:00 postmaster -i postgres 963 0.0 1.1 7084 1472 pts/1SN 13:17 0:00 postgres: stats buffer process postgres 965 0.0 1.1 6152 1512 pts/1SN 13:17 0:00 postgres: stats collector process postgres 998 0.0 2.3 6532 2992 pts/1SN 13:18 0:00 postgres: tgl runbug 127.0.0.1 idle postgres 1003 0.0 2.4 6532 3128 pts/1SN 13:19 0:00 postgres: tgl regression [local] SELECT waiting postgres 1016 0.1 2.4 6532 3080 pts/1SN 13:19 0:00 postgres: tgl regression [local] idle in transaction And each client connection has its command line which displays in form: postgres: user database host activity Is there any way to hide dbname and user name in displayed arguments of client connections? Thanks, Irina
Re: [GENERAL] Multithreaded query onto 4 postgresql instances
On 16 Feb 2011, at 9:54, Alessandro Candini wrote: >> Try the above on a single DB using 4 threads. It will very probably perform >> better. >> To use your example: >> 5432 ---> 150 million records >> 5432 ---> 150 million records >> 5432 ---> 150 million records >> 5432 ---> 150 million records >> > > Excuse me but query must to be performed on the whole db...with your > approach, how to merge results of every query in one single response? You have several options there. You can limit the query results by adding WHERE-clauses restricting each query to a particular set of data (partial indices may help there). Or you can split the database across multiple schema's, emulating the multi-database setup you have now. Or you can partition your tables (across multiple tablespaces would probably be a good idea). Plenty of options there. Alban Hertroys -- Screwing up is an excellent way to attach something to the ceiling. !DSPAM:737,4d5c16fc11737633677592! -- 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] Raid Controller Write Cache setting for WAL and Data
On Wed, Feb 16, 2011 at 04:40:43PM +0100, Piotr Gasidło wrote: > Is it safe, to have RAID controller (not BBC) write cache _enabled_ > for disks where data are stored and write cache _disabled_ for disks > where WAL segments are stored? No. > I _can_ afford of loosing some data in case of power failure. But I'm > afraid of having database in unrecoverable state after crash. It could be. Suppose you had the crash at the exact moment when the the WAL was getting flushed. Now you'll have a corrupt table. It will likely start up, but some time later when someone goes to get that data, you'll run into a problem. A battery is one of the simplest and cheapest things you can do to make your database system more reliable and faster at the same time. A -- Andrew Sullivan a...@crankycanuck.ca -- 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] disable triggers using psql
On Wed, Feb 16, 2011 at 10:08:53AM -0500, David Johnston wrote: > I may be off-track here but triggers do not enforce referential integrity - > constraints do. If you need to disable triggers you can do so via the ALTER > TABLE command. Unless something very big changed when I wasn't looking, the constraints are actually implemented as triggers under the hood. But you're right that it'd be cleaner to drop the constraints and re-add them than to fool with system triggers. > The reason I think pg_restore works for you is because when a table is built > using pg_restore all the data is loaded into all tables BEFORE any > constraints are created. I believe that if you did a data-only dump from > pg_dump you would have the same integrity problems. Yes. A -- Andrew Sullivan a...@crankycanuck.ca -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Raid Controller Write Cache setting for WAL and Data
Hello, Is it safe, to have RAID controller (not BBC) write cache _enabled_ for disks where data are stored and write cache _disabled_ for disks where WAL segments are stored? I _can_ afford of loosing some data in case of power failure. But I'm afraid of having database in unrecoverable state after crash. fsync = on I have 4 disks, 2 for WAL (RAID1) and 2 for data (RAID1). WAL and data are on XFS partitions (nobarrier, noatime). -- Piotr Gasidło -- 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] help understanding explain output
> Naturally a boolean can only have two values, really? pasman -- 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] finding bogus UTF-8
On Tue, Feb 15, 2011 at 5:06 PM, Geoffrey Myers wrote: > I toyed with tr for a bit, but could not get it to work. The above did not > work for me either. Not exactly sure what it's doing, but here's a couple > of diff lines: check your shell escaping. You may need \\ to protect the \ -- 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] disable triggers using psql
I may be off-track here but triggers do not enforce referential integrity - constraints do. If you need to disable triggers you can do so via the ALTER TABLE command. The reason I think pg_restore works for you is because when a table is built using pg_restore all the data is loaded into all tables BEFORE any constraints are created. I believe that if you did a data-only dump from pg_dump you would have the same integrity problems. You can manually get similar behavior by dropping table/column constraints and then re-creating them (and indexes) after the reload is complete. Primary Keys should remain permanently but since you do not want to violate those anyway the problem is not relevant. The only other option to consider is to make all the relevant constraints deferrable - though this may not always be possible. David J -Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Geoffrey Myers Sent: Wednesday, February 16, 2011 9:51 AM To: pgsql-general Subject: [GENERAL] disable triggers using psql So, we have a text dump that we used to clean up our data, now we need to reload it into the new database. Problem is, we have some data integrity issues that cause records to fail to load. Before we ran into the data conversion issue we were using 'pg_restore disable_triggers' to get around the data integrity issue. Is there a way to resolve this issue with the psql loading approach? -- Until later, Geoffrey "I predict future happiness for America if they can prevent the government from wasting the labors of the people under the pretense of taking care of them." - Thomas Jefferson -- 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] disable triggers using psql
On Wed, Feb 16, 2011 at 09:50:39AM -0500, Geoffrey Myers wrote: > Is there a way to resolve this issue with the psql loading approach? You can just disable or, depending on your version of Postgres, drop the triggers at the start of the load, load everything up, and then add them again. A -- Andrew Sullivan a...@crankycanuck.ca -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] disable triggers using psql
So, we have a text dump that we used to clean up our data, now we need to reload it into the new database. Problem is, we have some data integrity issues that cause records to fail to load. Before we ran into the data conversion issue we were using 'pg_restore disable_triggers' to get around the data integrity issue. Is there a way to resolve this issue with the psql loading approach? -- Until later, Geoffrey "I predict future happiness for America if they can prevent the government from wasting the labors of the people under the pretense of taking care of them." - Thomas Jefferson -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] server setup/testing performance
i'm looking for tools, like ab/siege/jmeter for web servers, to test postgres performance. not looking to resolve specific performance problems just to tune configuration to get average/better performance for server than with default installation. i did use instructions from http://wiki.postgresql.org/wiki/Performance_Optimization successfully on existing installations but i would like to have a few scripts that can test new postgres installation. any suggestions? Aljosa Mohorovic -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pg_dump with select output
On 02/16/2011 11:54 AM, Adarsh Sharma wrote: Dear all, I am using pg_dump in Postgresql database very often and read several parameters of it. But today i want to back up that part of table which satisfies satisfies certain condition ( select command ). In mysql , this is achieved as below : mysqldump -h192.168.1.106 -uroot -porkash -q -w"internalurl_id between 1 and 30" bicrawler internalurl > /root/Desktop/internal_url.sql -w option is used for executing select command . But don't know how this is achieved through pg_dump command. Not through pgdump, but you can do it with psql like such psql -h 192.168.1.106 -d bicrawler -o /root/Desktop/internal_url.sql -P format=unaligned -P fieldsep="|,|" -t -U postgres -c "select * from internalurl where internalurl_id between 1 and 30 " Note that you have to have postgres (or the user you are using) authenticated by trust or it will ask for a password. You can't put the password on the command line, though you can use a .pgpass file. Please help. Thanks & best Regards, Adarsh Sharma -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pg_dump with select output
On 16/02/2011 09:54, Adarsh Sharma wrote: Dear all, I am using pg_dump in Postgresql database very often and read several parameters of it. But today i want to back up that part of table which satisfies satisfies certain condition ( select command ). In mysql , this is achieved as below : mysqldump -h192.168.1.106 -uroot -porkash -q -w"internalurl_id between 1 and 30" bicrawler internalurl > /root/Desktop/internal_url.sql -w option is used for executing select command . But don't know how this is achieved through pg_dump command. You can't do this in pg_dump. pg_dump can backup: - the entire database - a schema, using -s - a table, using -t ...but not a subset of rows from a table. Maybe you want the COPY command? http://www.postgresql.org/docs/9.0/static/sql-copy.html Ray. -- Raymond O'Donnell :: Galway :: Ireland r...@iol.ie -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] pg_dump with select output
Dear all, I am using pg_dump in Postgresql database very often and read several parameters of it. But today i want to back up that part of table which satisfies satisfies certain condition ( select command ). In mysql , this is achieved as below : mysqldump -h192.168.1.106 -uroot -porkash -q -w"internalurl_id between 1 and 30" bicrawler internalurl > /root/Desktop/internal_url.sql -w option is used for executing select command . But don't know how this is achieved through pg_dump command. Please help. Thanks & best Regards, Adarsh Sharma -- 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] Multithreaded query onto 4 postgresql instances
Il 15/02/2011 19:32, Alban Hertroys ha scritto: On 15 Feb 2011, at 9:32, Alessandro Candini wrote: Is that a single query on that one DB compared to 4 queries on 4 DB's? How does a single DB with 4 parallel queries perform? I'd expect that to win from 4 DB's, due to the overhead those extra DB instances are generating. Maybe my configuration and test is not clear It is clear. I gave you another suggestion for something to try instead. Splitted instances: 600 millions of records in total splitted into 4 postgresql instances (port 5433, 5434, 5435, 5436), let's say more or less: 5433 ---> 150 millions of records 5434 ---> 150 millions of records 5435 ---> 150 millions of records 5436 ---> 150 millions of records Try the above on a single DB using 4 threads. It will very probably perform better. To use your example: 5432 ---> 150 million records 5432 ---> 150 million records 5432 ---> 150 million records 5432 ---> 150 million records Excuse me but query must to be performed on the whole db...with your approach, how to merge results of every query in one single response? Did you read all the way to section 35.9.10? That explains how to create SRF's like yours, including examples. If the step from simple functions to SRF's is too large for you, create a few sample-functions to learn how the intermediary steps work. We can't upload the info to your brain, after all - you'll have to do the learning part by yourself. Do you think is a good idea continue using libpq or should I abandon them and focus on postgresql documentation examples? To learn how to write functions like these? Best to use a simple case so it's clear what's going on. If you try to alter already complicated code for that purpose you'll probably just get more confused. Alban Hertroys -- Screwing up is an excellent way to attach something to the ceiling. !DSPAM:1234,4d5ac6b211738438191676! -- Alessandro Candini MEEO S.r.l. Via Saragat 9 I-44122 Ferrara, Italy Tel: +39 0532 1861501 Fax: +39 0532 1861637 http://www.meeo.it "ATTENZIONE:le informazioni contenute in questo messaggio sono da considerarsi confidenziali ed il loro utilizzo è riservato unicamente al destinatario sopra indicato. Chi dovesse ricevere questo messaggio per errore è tenuto ad informare il mittente ed a rimuoverlo definitivamente da ogni supporto elettronico o cartaceo." "WARNING:This message contains confidential and/or proprietary information which may be subject to privilege or immunity and which is intended for use of its addressee only. Should you receive this message in error, you are kindly requested to inform the sender and to definitively remove it from any paper or electronic format." -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] why does the toast table exist?
I found in my Postgresql 9.0.1 DB as follows: select oid,relname,reltoastrelid,relpages,relfilenode,reltuples from pg_class where oid in ( 90662,90665); -[ RECORD 1 ]-+--- oid | 90662 relname | audit_trial reltoastrelid | 90665 relpages | 7713 relfilenode | 373748 reltuples | 930648 -[ RECORD 2 ]-+--- oid | 90665 relname | pg_toast_90662 reltoastrelid | 0 relpages | 0 relfilenode | 373751 reltuples | 0 I have no idea why the TOAST table exists for audit_trial table. \d audit_trial Table "public.audit_trial" Column |Type | Modifiers +-+--- auditid| integer | not null userid | integer | module | character varying(255) | action | character varying(255) | recordid | character varying(20) | actiondate | timestamp without time zone | Indexes: "audit_trial_pkey" PRIMARY KEY, btree (auditid) "audit_trial_action_idx" btree (action) "audit_trial_actiondate_desc_idx" btree (actiondate DESC) CLUSTER "audit_trial_module_idx" btree (module) "audit_trial_userid_idx" btree (userid)* *
