Re: [GENERAL] Insert value input syntax of an array of types without ARRAY/ROW nor casting?
On 19 Mar 2011, at 2:33, Stefan Keller wrote:
Given the test snippet below, why do the following insert attempts fail?
The literal constant would be the most intuitive syntax. The attempt
below also closely follows the documentation AFAIK:
http://www.postgresql.org/docs/current/static/arrays.html
INSERT INTO mytypetable VALUES (
5, '{ ('a', 'aa'), ('b', 'bb') }' );
ERROR: Syntax Error
You need to escape those quotes you put inside the literal:
INSERT INTO mytypetable VALUES (
5, '{ (''a'', ''aa''), (''b'', ''bb'') }' );
Or use dollar-quoting
(http://www.postgresql.org/docs/current/static/sql-syntax-lexical.html#SQL-SYNTAX-DOLLAR-QUOTING):
INSERT INTO mytypetable VALUES (
5, $${ ('a', 'aa'), ('b', 'bb') }$$ );
Alban Hertroys
--
Screwing up is an excellent way to attach something to the ceiling.
!DSPAM:737,4d848304235883070015353!
--
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 taking much longer since Postgres 8.4 upgrade
Dne 18.3.2011 19:18, Merlin Moncure napsal(a): On Fri, Mar 18, 2011 at 10:42 AM, Davenport, Julie jdavenp...@ctcd.edu wrote: This helped, is now down from 14.9 min to 10.9 min to run the entire script. Thanks. can you try disabling nestloop and see what happens? In the session, before running the query, isssue: set enable_nestloop = false; Yes, that'd be interesting. And provide 'exaplain analyze' as before (using explain.depesz.com), please. And a bit unrelated recommendation - based on the settings (cost constants, work_mem etc.) it seems guess you have a default untuned postgresql.conf. Is that right, Julie? In this case you can significantly improve the load performance by several settings: 1) increase checkpoint_segments (default is 3, use 12 or something like that - depends on the if there are warnings about checkpoint segments in the log) 2) increase wal_buffers (just set it to 16MB and forget it) The effect depends on the amount of data loaded and other things, but it's worth a try. regards Tomas -- 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] Insert value input syntax of an array of types without ARRAY/ROW nor casting?
Thank you for the hint.
Unfortunately it still does'nt work. I get
ERROR: wrong record constant: »('a'«
LINE 2: 5, $${ ('a', 'aa'), ('b', 'bb') }$$ );
^
DETAIL: Unexpected end of line.
Yours, S.
2011/3/19 Alban Hertroys dal...@solfertje.student.utwente.nl:
On 19 Mar 2011, at 2:33, Stefan Keller wrote:
Given the test snippet below, why do the following insert attempts fail?
The literal constant would be the most intuitive syntax. The attempt
below also closely follows the documentation AFAIK:
http://www.postgresql.org/docs/current/static/arrays.html
INSERT INTO mytypetable VALUES (
5, '{ ('a', 'aa'), ('b', 'bb') }' );
ERROR: Syntax Error
You need to escape those quotes you put inside the literal:
INSERT INTO mytypetable VALUES (
5, '{ (''a'', ''aa''), (''b'', ''bb'') }' );
Or use dollar-quoting
(http://www.postgresql.org/docs/current/static/sql-syntax-lexical.html#SQL-SYNTAX-DOLLAR-QUOTING):
INSERT INTO mytypetable VALUES (
5, $${ ('a', 'aa'), ('b', 'bb') }$$ );
Alban Hertroys
--
Screwing up is an excellent way to attach something to the ceiling.
!DSPAM:1205,4d848300235885070126629!
--
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] triggers and FK cascades
Le vendredi 18 mars 2011 à 08:20 +, Grzegorz Jaśkiewicz a écrit : There's a generic trigger that sends a signal to a process whenever changes are made (via listen/notify mechanism), but when FK cascade fires it will cause a mass amount of notifies to be send out and I want to avoid it. I have no personal experience with this, but citing an answer to a similar question by Vibhor Kumar from a couple days ago : use following command: ALTER TABLE tablename DISABLE TRIGGER [ trigger_name | ALL | USER ] -- Vincent Veyron http://marica.fr/ Logiciel de gestion des sinistres pour le service juridique -- 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] Insert value input syntax of an array of types without ARRAY/ROW nor casting?
On Mar 19, 2011, at 9:50 PM, Stefan Keller wrote:
Unfortunately it still does'nt work. I get
ERROR: wrong record constant: »('a'«
LINE 2: 5, $${ ('a', 'aa'), ('b', 'bb') }$$ );
^
DETAIL: Unexpected end of line.
Try following, it should work:
INSERT INTO mytypetable VALUES (
6,ARRAY[row('a', 'aa'), row('b',
'bb')]::mytype[] );
Thanks Regards,
Vibhor Kumar
EnterpriseDB Corporation
The Enterprise PostgreSQL Company
vibhor.ku...@enterprisedb.com
Blog:http://vibhork.blogspot.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] How do I do this in plpgsql ?
On Mar 19, 2011, at 12:50 AM, Dan S wrote: For example if I have a type sample%TYPE How can I declare a variable that is an array of sample%TYPE I can't get it to work, is there a way to do it ? Nope, there is no way. However, you can create a userdefined type and can also use array of that http://www.postgresql.org/docs/8.4/static/sql-createtype.html Thanks Regards, Vibhor Kumar EnterpriseDB Corporation The Enterprise PostgreSQL Company vibhor.ku...@enterprisedb.com Blog:http://vibhork.blogspot.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] WAL shipping replication server re-sync
Hey folks, We're using PG 8.4.7 on two servers that are geographically distant from each other. We run WAL-shipping replication (i.e. constant recovery mode replication) between the two servers. These are the only two servers involved in the setup. When we do the 'fail-over' to swap the master and slave servers we perform the usual recovery trigger for pg_standby and then wait. The slave now becomes the master and the master becomes the new slave. This works great. When the servers flip modes we have been following the guidelines that say to clear the WAL logs and the main data area of the slave and copy the data area of the new master to the slave while the master is in 'backup' mode. Given the current size (17GB) of this data area and the bandwidth limits of the 'inter-tubes', this takes a rather long time. (4.5 hrs last time) Here's my (naive?) question: Since the two servers were essentially 'in sync' immediately prior to the switch- over is it possible to simply rsync the new master's data area back to the new slave's data area without clearing the slave's data area first? Or is the directory and file structure sufficiently different (even if the logical data is identical) to preclude this? Could this difference be alleviated by using the various 'delete on destination' options for rsync? TIA, Bosco. -- 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 do you stop the log from rotating on restart?
Postgres 9.0.3 on Solaris 10 Can't seem to figure out how to stop the postgres log file from rotating on a restart of services. log_truncate_on_rotation is commented out log_rotation_age = 1d We just want it to rotate once a day no matter haw many times we restart. Don't see anything else that would seem to affect log file rotation
[GENERAL] Database Design for Components and Interconnections
I am looking for some help in database design. I would like to design a database to help design alternative designs of a basic electronic circuit design. I have a list of components that will be interconnected for a basic design. Additional components and associated connections are identified for different alternatives. The connections have properties that must be managed. The typical use is to implement a new design where a specific set of components is identified and the associated interconnects need to be managed. Additionally, these two sets of data will be copied to another application for analysis. The connection information is a matrix where the row and column ‘labels’ are elements of the components table. The matrix elements define the interconnections between the components. In the simplest case, the interconnection matrix elements are just either -1, 0, or 1, defining whether or not there is a connection between the two components and the direction of the connection. In the more realistic cases, there are many properties of each interconnection so this is a three dimensional matrix. As for performance, this database will be accessed by at most 20 people at one time where they are addressing disjoint properties. The number of components will be a couple thousand. The average number of interconnections of any one component to other components is 6 so the matrix may be considered sparse. I usually use a spreadsheet for the component definitions and multiple spreadsheets (tabs) for each of the tables in the third dimension. Then save the needed interconnection info as a CSV file for import into other applications. I will appreciate any suggestions, insights, questions and comments. Thanks, ray -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
