[GENERAL] Database denormalization
Hi I would like to ask weather PostgreSQL does database denormalization at runtime. That is, for example, if I have a normalized database and I use lots of querys that would run faster on a denormalized database, than will PostgreSQL create a denormalized version of the database for internal use. To specify further, the question is, can I count on PostgreSQL to denormalize the database when it would be better for the performance, or should I always denormalize the database and all the querys myself. I have looked for answers on the subject, but all I managed to find was a wiki article at http://en.wikipedia.org/wiki/Denormalization that says: The preferred method is to keep the logical design normalised, but allow the database management system (DBMS) to store additional redundant information on disk to optimise query response. In this case it is the DBMS software's responsibility to ensure that any redundant copies are kept consistent. This method is often implemented in SQL as indexed views (Microsoft SQL Server) or materialised views (Oracle). So in the case of PostgreSQL, do I also have to use views or are there some other ways? 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] Database denormalization
On Mon, Feb 13, 2012 at 15:48, JG vh...@rocketmail.com wrote: I would like to ask weather PostgreSQL does database denormalization at runtime. To specify further, the question is, can I count on PostgreSQL to denormalize the database when it would be better for the performance, or should I always denormalize the database and all the querys myself. Even the Oracle and MSSQL features you mention, don't denormalize the database themselves -- you have to design and query from those indexed/materialized views manually. But no, PostgreSQL does not natively support materialized views, so it's probably easier to work with a denormalized schema to begin with. Or you can create denormalized copies of your data and keep it in sync yourself -- via triggers or periodically regeneretaing the whole materialized copy. (Normal indexes are technically also a denormalization technique; obviously PostgreSQL supports those ;) I have looked for answers on the subject, but all I managed to find was a wiki article at http://en.wikipedia.org/wiki/Denormalization that says: The preferred method is to keep the logical design normalised, but allow the database management system (DBMS) to store additional redundant information on disk to optimise query response. [...] This sounds good in theory, but as always, these features have their costs. So it's a tradeoff over performance. Regards, Marti -- 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] [GENERA]: Postgresql-9.1.1 synchronous replication issue
Hello, Disaster Recovery testing for Synchronous replication setup - When the standby site is down, transactions at the production site started hanging (this is after the successful setup of synchronous replication). We changed synchronous_commit to 'local' to over-come this situation. - No transactions are hanging at the production site even when the standby is down - Standby is automatically getting synced when it is back up again. Can someone let us know if there are any -ve effects of putting synchronous_commit='local' ?? I am assuming that this as good as putting synchronous_commit=on on an stand-alone system. We need to get this setup live on production shortly. Thanks VB On Fri, Feb 10, 2012 at 4:47 PM, Venkat Balaji venkat.bal...@verse.inwrote: This issue stays resolved !!! The statements are no more hanging on production now :) The suspected problem was - Our brand new production server did not have the port 5432 open. I had opened the port using iptables command and everything started working. synchronous replication is fast and awesome. Thanks VB On Fri, Feb 3, 2012 at 9:45 PM, Adrian Klaver adrian.kla...@gmail.comwrote: On Thursday, February 02, 2012 10:21:28 pm Venkat Balaji wrote: Connection is working fine between primary and standby, ping is working fine and wal archive file transfer is working without any issues. I tried CREATE TABLE and CREATE DATABASE, both were hanging. Apart from regular streaming replication settings, I did the following on primary to enable synchronous replication - synchronous_standby_names='*' Commands started hanging after that. Is there anything else i need to do. From here: http://www.postgresql.org/docs/9.1/interactive/runtime-config-replication.html synchronous_standby_names (string) ... The synchronous standby will be the first standby named in this list that is both currently connected and streaming data in real-time (as shown by a state of streaming in the pg_stat_replication view). Other standby servers appearing later in this list represent potential synchronous standbys The name of a standby server for this purpose is the application_name setting of the standby, as set in the primary_conninfo of the standby's walreceiver. There is no mechanism to enforce uniqueness. In case of duplicates one of the matching standbys will be chosen to be the synchronous standby, though exactly which one is indeterminate. The special entry * matches any application_name, including the default application name of walreceiver. So I would check the pg_stat_replication view to see if Postgres is seeing the standby as streaming. Thanks VB -- Adrian Klaver adrian.kla...@gmail.com
Re: [GENERAL] Database denormalization
JG wrote: To specify further, the question is, can I count on PostgreSQL to denormalize the database when it would be better for the performance, or should I always denormalize the database and all the querys myself. PostgreSQL does not do such things automatically. You'll have to do so yourself. I have looked for answers on the subject, but all I managed to find was a wiki article at http://en.wikipedia.org/wiki/Denormalization that says: The preferred method is to keep the logical design normalised, but allow the database management system (DBMS) to store additional redundant information on disk to optimise query response. In this case it is the DBMS software's responsibility to ensure that any redundant copies are kept consistent. This method is often implemented in SQL as indexed views (Microsoft SQL Server) or materialised views (Oracle). So in the case of PostgreSQL, do I also have to use views or are there some other ways? Views won't help you. A materialized view is actually a table that holds a (possibly aggregated) copy of data from elsewhere in the database. Apart from materialized views, you can denormalize for performance by adding columns to tables that store a copy of information from another table, with the benefit that you can avoid joins to access the information. The problem you have to solve when you use denormalization techniques is to keep the data and the copy consistent. Yours, Laurenz Albe -- 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] Let-bindings in SQL statements
On Sat, Feb 11, 2012 at 12:42, Jasen Betts ja...@xnet.co.nz wrote: There is no need. now() is tagged as stable. it will only be executed once. the planner will figure this out for you. Actually that's not always true. In index condition arguments, the expression would indeed be executed just once. But in filter clauses (e.g. seq scan), the whole expression is executed once per row, which is a bit inefficient. Of course this makes no visible difference for now(), since it always returns the same value -- the transaction start time. (I submitted a patch to improve this, but it's not certain whether it will be included in PostgreSQL 9.2 or not) Regards, Marti -- 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] [GENERA]: Postgresql-9.1.1 synchronous replication issue
On Tuesday, February 14, 2012 4:21:22 am Venkat Balaji wrote: Hello, Disaster Recovery testing for Synchronous replication setup - When the standby site is down, transactions at the production site started hanging (this is after the successful setup of synchronous replication). We changed synchronous_commit to 'local' to over-come this situation. - No transactions are hanging at the production site even when the standby is down - Standby is automatically getting synced when it is back up again. Can someone let us know if there are any -ve effects of putting synchronous_commit='local' ?? I am assuming that this as good as putting synchronous_commit=on on an stand-alone system. It would seem you are really after streaming replication(which is asynchronous) more than synchronous replication. I have not used synchronous replication enough to be sure, but I think by setting synchronous_commit='local' you are basically turning the system into a straight streaming(asynchronous) system anyway. We need to get this setup live on production shortly. Thanks VB -- 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: [GENERAL] Database denormalization
On Tue, Feb 14, 2012 at 6:27 AM, Albe Laurenz laurenz.a...@wien.gv.at wrote: A materialized view is actually a table that holds a (possibly aggregated) copy of data from elsewhere in the database. Apart from materialized views, you can denormalize for performance by adding columns to tables that store a copy of information from another table, with the benefit that you can avoid joins to access the information. The standard reference for materialized views is the page by j gardner: http://tech.jonathangardner.net/wiki/PostgreSQL/Materialized_Views -- 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_upgrade: out of memory
On Mon, Feb 06, 2012 at 05:14:55PM -0500, deepak wrote: Hi! While running pg_upgrade, on one instance, it ran out of memory during the final stages of upgrade (just before it starts to link old database files to new ones). We are using Postgres 9.1.1, and I see that there were some fixes to pg_upgrade in 9.1.2, though it doesn't mention anything about memory issues as such. Wondering if anyone has run into a similar type of situation, and if so, how to solve it? Also, is it possible to recover the database once pg_upgrade aborts in this manner? Here's an excerpt from the log while running pg_upgrade: ... bin/pg_ctl -w -D data stop waiting for server to shut downLOG: received smart shutdown request LOG: shutting down ...LOG: database system is shut down . done server stopped Restoring user relation files Restoring user relation files pg_upgrade: out of memory pg_upgrade: out of memory Wow, that's pretty amazing. As the docs say, pg_upgrade doesn't modify the old cluster so you should be fine in reverting to that. As far as why it ran out of memory, I have no idea, and have never heard of a report about that before. Can you run it in a debugger and get a backtrace? -- Bruce Momjian br...@momjian.ushttp://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] High checkpoint_segments
We need to do a few bulk updates as Rails migrations. We're a typical read-mostly web site, so at the moment, our checkpoint settings and WAL are all default (3 segments, 5 min, 16MB), and updating a million rows takes 10 minutes due to all the checkpointing. We have no replication or hot standbys. As a consumer-web startup, with no SLA, and not a huge database, and if we ever do have to recover from downtime it's ok if it takes longer.. is there a reason NOT to always run with something like checkpoint_segments = 1000, as long as I leave the timeout at 5m? Jay Levitt -- 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] psql latex and newlines
On Mon, Feb 13, 2012 at 02:50:12PM +0100, Wim Bertels wrote: On vr, 2012-02-10 at 19:25 -0500, Bruce Momjian wrote: On Mon, Dec 12, 2011 at 07:15:12PM +0100, Wim Bertels wrote: Hallo, psql latex output format needs to differentiate between a newline and a tabularnewline. the problem arises when u have a field value that contains a newline character, when this field is not the first column, then all the data after this newline comes in the first column.. u can try this out, writing a function or table, and then add 'enters' or newline in the COMMENT on this function or table. the \pset recordsep doesn't solve this, since the mistakes take place within the same record. Can you give me a self-contained test case I can use so I can use it to fix the to code? Hi Bruce, i have attached some files: 1 sql file 1 corresponding output file 1 full latex file using the output of the above file i don't see and easy search/replace fix for this problem, maybe using the \multirow and \multicolumn options in latex is the most general solution, as all the others seems to need concrete dimensions (as width) http://www.google.com/search?client=ubuntuchannel=fsq=newline+tabular +cell+latexie=utf-8oe=utf-8 http://andrewjpage.com/index.php?/archives/43-Multirow-and-multicolumn-spanning-with-latex-tables.html There is also a problem with long lines as u can test by uncommenting the t4bruce3 function. Well, I was hoping you could tell me exactly how you wanted the behavior changed and I could try to implement it in psql. I am afraid I don't know enough about TeX to understand the isssue involved. -- Bruce Momjian br...@momjian.ushttp://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] [GENERA]: Postgresql-9.1.1 synchronous replication issue
On Tue, Feb 14, 2012 at 8:09 PM, Adrian Klaver adrian.kla...@gmail.comwrote: On Tuesday, February 14, 2012 4:21:22 am Venkat Balaji wrote: Hello, Disaster Recovery testing for Synchronous replication setup - When the standby site is down, transactions at the production site started hanging (this is after the successful setup of synchronous replication). We changed synchronous_commit to 'local' to over-come this situation. - No transactions are hanging at the production site even when the standby is down - Standby is automatically getting synced when it is back up again. Can someone let us know if there are any -ve effects of putting synchronous_commit='local' ?? I am assuming that this as good as putting synchronous_commit=on on an stand-alone system. It would seem you are really after streaming replication(which is asynchronous) more than synchronous replication. I have not used synchronous replication enough to be sure, but I think by setting synchronous_commit='local' you are basically turning the system into a straight streaming(asynchronous) system anyway. Yeah. Its a kind of asynchronous. All i wanted is as follows - 1 We need to get this setup live on production shortly. Thanks VB -- Adrian Klaver adrian.kla...@gmail.com
[GENERAL] Postgresql 9.0.6 backends pruning process environment?
djenkins@ostara ~/code/capybara $ psql -U$someuser -dpostgres -c
select version();
version
--
PostgreSQL 9.0.6 on x86_64-pc-linux-gnu, compiled by GCC
x86_64-pc-linux-gnu-gcc (Gentoo 4.5.3-r1 p1.0, pie-0.4.5) 4.5.3,
64-bit
(1 row)
djenkins@ostara ~/code/capybara $ uname -a
Linux ostara 3.1.6-gentoo #1 SMP PREEMPT Mon Jan 9 22:43:24 CST 2012
x86_64 Intel(R) Core(TM) i5 CPU 760 @ 2.80GHz GenuineIntel GNU/Linux
I recently updated my Gentoo Linux development system from postgresql
9.0.4 to 9.0.6-r1 (9.0.6 plus some Gentoo specific patches). One of
my 'C' language functions (been using it for years) stopped working
because the backend no longer had access to the PGDATA environment
variable. A snippet of code is included below.
My routine gathers some data about the file system that the base
data directory resides on (ignoring table-spaces that could move data
to other file systems). The existing postgresql server admin
functions are not sufficient to accomplish my goal:
1) pg_database_size does not give me all of the info that I'm after.
2) pg_relation_filepath only returns the path relative to PGDATA
(eg, base/n/m, not what I'm after
(/var/lib/postgresql/9.0, but may vary from system to system).
Development on 8.4.4 through 9.0.4 worked fine. getenv(PGDATA)
returned a valid pathname in a shared object C function when ran by
the back end.
9.0.6 (and 9.0.6-r1) backends appear to have no environment variables
set in their backends.
Gentoo's portage no longer has an ebuild for 9.0.4, so I reverted to
9.0.5. My function resumed working again.
I then tried Gentoo's portage for postgresql-9.0.6 (no -r1) and it
failed the same (the env var is not available to the forked backend)
For each postgresql version test, I recompiled and re-installed my
function (a '.so' file).
I skimmed the errata for Postgresql-9.0.6 and could not find anything
relevant. (http://www.postgresql.org/docs/9.0/static/release-9-0-6.html)
I tried digging around in a mirrored source repository
(https://github.com/postgres/postgres/tree/master/src), but didn't
make much headway.
Thank you for your time and thoughts.
Questions:
1) Is the envvar present, and somehow my code or development system is faulty?
2) Were the envvars of the backends purposefully removed in version 9.0.6?
3) Is there a formal way to get the location of the pg data dir from
a C language routine?
4) It seems that the cwd (/prod/self/cwd sym link) would give me
what I need, but can this behavior be relied on for future versions of
Postgresql on Linux?
ostara ~ # ls -l /proc/2384/cwd
lrwxrwxrwx 1 postgres postgres 0 Feb 14 23:38 /proc/2384/cwd -
/var/lib/postgresql/9.0/data
ostara ~ # cat /proc/2384/environ
ostara ~ # equery l '*postgresql*'
* Searching for *postgresql* ...
[IP-] [ ] app-admin/eselect-postgresql-1.0.10:0
[IP-] [ ] dev-db/postgresql-base-9.0.6:9.0
[IP-] [ ] dev-db/postgresql-base-9.1.2:9.1
[IP-] [ ] dev-db/postgresql-server-9.0.6:9.0
[IP-] [ ] dev-db/postgresql-server-9.1.2-r2:9.1
Datum backend_disk_stats (PG_FUNCTION_ARGS)
{
char*pgdatadir = NULL;
struct statvfs vfs;
TupleDesc tupdesc = NULL; // Custom PG data type
disk_stats
AttInMetadata *attinmeta = NULL; // Used for accessing
composit type members by name.
Datum result;
HeapTuple tuple;
char**values = NULL;
int i = 0;
u_int64_t nUsed = 0;
if (NULL == (pgdatadir = getenv (PGDATA)))
{
elog (ERROR, getenv('PGDATA') failed.\n);
PG_RETURN_NULL ();
}
if (-1 == statvfs (pgdatadir, vfs))
{
elog (ERROR, statvfs() failed.\n);
PG_RETURN_NULL ();
}
--
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] [GENERA]: Postgresql-9.1.1 synchronous replication issue
On Wed, Feb 15, 2012 at 11:01 AM, Venkat Balaji venkat.bal...@verse.inwrote: On Tue, Feb 14, 2012 at 8:09 PM, Adrian Klaver adrian.kla...@gmail.comwrote: On Tuesday, February 14, 2012 4:21:22 am Venkat Balaji wrote: Hello, Disaster Recovery testing for Synchronous replication setup - When the standby site is down, transactions at the production site started hanging (this is after the successful setup of synchronous replication). We changed synchronous_commit to 'local' to over-come this situation. - No transactions are hanging at the production site even when the standby is down - Standby is automatically getting synced when it is back up again. Can someone let us know if there are any -ve effects of putting synchronous_commit='local' ?? I am assuming that this as good as putting synchronous_commit=on on an stand-alone system. It would seem you are really after streaming replication(which is asynchronous) more than synchronous replication. I have not used synchronous replication enough to be sure, but I think by setting synchronous_commit='local' you are basically turning the system into a straight streaming(asynchronous) system anyway. Sorry. Ignore my earlier message - Yeah. Its a kind of asynchronous ( at the transaction level, NOT WAL based ). All i wanted to achieve is as follows - 1. Synchronous replication - which would perform transactions simultaneously on production and standby. 2. Ideally, if the commit does not occur at the standby site, then it would not commit at the production as well, which will cause production site to hang. I do not want production site to hang if the standby site is down or not accessible. 3. I would need the commit to occur on production and the production apps should not be disturbed if the standby fails to respond. To achieve this, I have set synchronous_commit='local' to ensure that transactions are committed at production site first. We do have streaming replication (of PG-9.0) setup on our other production boxes, which is asynchronous and is WAL based. Thanks VB
[GENERAL] Easy form of insert if it isn't already there?
Periodically I find myself wanting to insert into some table, specifying the primary key column(s), but to simply ignore the request if it's already there. Currently I have two options: 1) Do the insert as normal, but suppress errors. SAVEPOINT foo; INSERT INTO table (col1,col2,col3) VALUES (val1,val2,val3); (if error) ROLLBACK TO SAVEPOINT foo; 2) Use INSERT... SELECT: INSERT INTO table (col1,col2,col3) SELECT val1,val2,val3 WHERE NOT EXISTS (SELECT * FROM table WHERE col1=val1 AND col2=val2) The former makes unnecessary log entries, the latter feels clunky. Is there some better way? All tips appreciated! Chris Angelico -- 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] High checkpoint_segments
On Wed, Feb 15, 2012 at 1:35 AM, Jay Levitt jay.lev...@gmail.com wrote: We need to do a few bulk updates as Rails migrations. We're a typical read-mostly web site, so at the moment, our checkpoint settings and WAL are all default (3 segments, 5 min, 16MB), and updating a million rows takes 10 minutes due to all the checkpointing. We have no replication or hot standbys. As a consumer-web startup, with no SLA, and not a huge database, and if we ever do have to recover from downtime it's ok if it takes longer.. is there a reason NOT to always run with something like checkpoint_segments = 1000, as long as I leave the timeout at 5m? Still checkpoints keep occurring every 5 mins. Anyways checkpoint_segments=1000 is huge, this implies you are talking about 16MB * 1000 = 16000MB worth pg_xlog data, which is not advisable from I/O perspective and data loss perspective. Even in the most unimaginable case if all of these 1000 files get filled up in less than 5 mins, there are chances that system will slow down due to high IO and CPU. You may think of increasing checkpoint_timeout as well, but, some monitoring and analysis is needed to arrive at a number. What does pg_stat_bgwriter say about checkpoints ? Do you have log_checkpoints enabled ? Thanks VB
Re: [GENERAL] Easy form of insert if it isn't already there?
Hi, similar topic is in NOVICE mailing list: http://archives.postgresql.org/pgsql-novice/2012-02/msg00034.php e.g. You can use BEGIN... EXCEPTION END, good example of such approach is there: http://www.postgresql.org/docs/9.1/static/plpgsql-control-structures.html#PLPGSQL-UPSERT-EXAMPLE ; Regards, Bartek 2012/2/15 Chris Angelico ros...@gmail.com Periodically I find myself wanting to insert into some table, specifying the primary key column(s), but to simply ignore the request if it's already there. Currently I have two options: 1) Do the insert as normal, but suppress errors. SAVEPOINT foo; INSERT INTO table (col1,col2,col3) VALUES (val1,val2,val3); (if error) ROLLBACK TO SAVEPOINT foo; 2) Use INSERT... SELECT: INSERT INTO table (col1,col2,col3) SELECT val1,val2,val3 WHERE NOT EXISTS (SELECT * FROM table WHERE col1=val1 AND col2=val2) The former makes unnecessary log entries, the latter feels clunky. Is there some better way? All tips appreciated! Chris Angelico -- 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] Easy form of insert if it isn't already there?
On Wed, Feb 15, 2012 at 5:26 PM, Bartosz Dmytrak bdmyt...@eranet.pl wrote: Hi, similar topic is in NOVICE mailing list: http://archives.postgresql.org/pgsql-novice/2012-02/msg00034.php e.g. You can use BEGIN... EXCEPTION END, good example of such approach is there: http://www.postgresql.org/docs/9.1/static/plpgsql-control-structures.html#PLPGSQL-UPSERT-EXAMPLE; Ah, thanks for that! Currently the query is a single PHP pg_query_params() call, and it's inside a larger transaction. By the look of it, this requires writing a function to do the job, rather than embedding the logic straight into the query - is this correct? ChrisA -- 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] High checkpoint_segments
On Tue, Feb 14, 2012 at 10:57 PM, Venkat Balaji venkat.bal...@verse.in wrote: On Wed, Feb 15, 2012 at 1:35 AM, Jay Levitt jay.lev...@gmail.com wrote: We need to do a few bulk updates as Rails migrations. We're a typical read-mostly web site, so at the moment, our checkpoint settings and WAL are all default (3 segments, 5 min, 16MB), and updating a million rows takes 10 minutes due to all the checkpointing. We have no replication or hot standbys. As a consumer-web startup, with no SLA, and not a huge database, and if we ever do have to recover from downtime it's ok if it takes longer.. is there a reason NOT to always run with something like checkpoint_segments = 1000, as long as I leave the timeout at 5m? Still checkpoints keep occurring every 5 mins. Anyways checkpoint_segments=1000 is huge, this implies you are talking about 16MB * 1000 = 16000MB worth pg_xlog data, which is not advisable from I/O perspective and data loss perspective. Even in the most unimaginable case if all of these 1000 files get filled up in less than 5 mins, there are chances that system will slow down due to high IO and CPU. As far as I know there is no data loss issue with a lot of checkpoint segments. -- 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] Easy form of insert if it isn't already there?
Yes it is. You can implement trigger on table to check if inserted record is new. Still it is on DB side. I don't know PHP well enough but I think You can call function e.g. SELECT myschema.InsertWhenNew (val1, val2, val3); in the same way as You call INSERTS Regards, Bartek 2012/2/15 Chris Angelico ros...@gmail.com On Wed, Feb 15, 2012 at 5:26 PM, Bartosz Dmytrak bdmyt...@eranet.pl wrote: Hi, similar topic is in NOVICE mailing list: http://archives.postgresql.org/pgsql-novice/2012-02/msg00034.php e.g. You can use BEGIN... EXCEPTION END, good example of such approach is there: http://www.postgresql.org/docs/9.1/static/plpgsql-control-structures.html#PLPGSQL-UPSERT-EXAMPLE ; Ah, thanks for that! Currently the query is a single PHP pg_query_params() call, and it's inside a larger transaction. By the look of it, this requires writing a function to do the job, rather than embedding the logic straight into the query - is this correct? ChrisA -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
