Re: [GENERAL] Slow connection once the PC is network connected
Thanks. That's work pretty well.
--- On Tue, 10/26/10, Alban Hertroys
wrote:
> From: Alban Hertroys
> Subject: Re: [GENERAL] Slow connection once the PC is network connected
> To: "Yan Cheng CHEOK"
> Cc: pgsql-general@postgresql.org
> Date: Tuesday, October 26, 2010, 6:01 PM
> On 26 Oct 2010, at 11:39, Yan Cheng
> CHEOK wrote:
>
> > Hello, I am deploying a database to customer PC, which
> will be only connected by local application.
> >
> > However, I realize once the PC is network connected,
> the connection take extremely long time (around 10 seconds)
>
> That sounds suspiciously like the default timeout for DNS
> lookups.
> What platform is this? Windows?
> Is that machine able to look up localhost?
>
> > May I know why this happen? I was wondering whether is
> there any workaround to overcome this?
> >
> > I connect the database using C++ code
> >
> > PQconnectdb("dbname = ProductionFactory user =
> postgres password = password");
>
>
> Does it help any to add hostaddr = 127.0.0.1 to that
> string?
>
> Alban Hertroys
>
> --
> Screwing up is an excellent way to attach something to the
> ceiling.
>
>
> !DSPAM:737,4cc6a70710292409920920!
>
>
>
> --
> 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] How to determine server version inside select statement
On 31 Oct 2010, at 22:56, Andrus wrote: > I tried > > SELECT (SHOW server_version) AS Contents postgres=> select version(); version - PostgreSQL 8.4.4 on i386-portbld-freebsd7.2, compiled by GCC cc (GCC) 4.2.1 200 70719 [FreeBSD], 32-bit (1 row) Alban Hertroys -- Screwing up is an excellent way to attach something to the ceiling. !DSPAM:737,4ccdfa2010261036599539! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] avoiding nested loops when joining on partitioned tables
Hi, I have two tables (A and B), which are partitioned (A1, A2... B1, B2...) for easy deletion of old records. They are linked by a bigint column "id", which is defined as a foreign key in each B partition referencing the corresponding A partition. Many rows in B1 can reference a single row in A1. The "id" column is indexed in both tables. Each partition could have >1million rows. The id column in each A partition gets its nextval from a (common) counter, and the inserts always use the default value for this column - I know that B1 references rows in A1 only, B2 -> A2 only etc. Is there any way I can explain this to postgres? When I query the parent table of the partitions, "SELECT * from A, B where a.id=b.id;", the planner does a sequential scan on A, A1, A2, ... an index scan on B, B1, B2, ... then a nested loop, which generally takes a while. As I say, I presume this is because the planner does not know that there is no overlap in 'id' values between the different partitions - is there any way to express this? Thanks, Pete ps please copy me on replies as I am not subscribed.
Re: [GENERAL] How to determine server version inside select statement
2010/10/31 Andrus :
> I tried
>
> SELECT (SHOW server_version) AS Contents
>
> but got
>
> ERROR: syntax error at or near "server_version" at character 14
>
>
> how to get server version inside select statement ?
>
SELECT current_setting('server_version') AS Contents;
Osvaldo
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] How to determine server version inside select statement
I tried SELECT (SHOW server_version) AS Contents but got ERROR: syntax error at or near "server_version" at character 14 how to get server version inside select statement ? Andrus. -- 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] Sorting rows by a column and storing a row number
Use the Postgres window functions like rank(); this is what they're for. http://www.postgresql.org/docs/8.4/interactive/queries-table-expressions.html#QUERIES-WINDOW -- Darren Duncan Alexander Farber wrote: Hello, I have a card game for each I'd like to introduce weekly tournaments. I'm going to save the score (virtual money) won by each player into: create table pref_money ( id varchar(32) references pref_users, yw char(7) default to_char(current_timestamp, '-WW'), money real ); create index pref_money_yw_index on pref_money(yw); This way I don't have to perform any special calculations at the end of a week to find the weekly winner - just select all records for the current year-week, sort them by "money" column and take the 1st one. But I wonder, if there is a nice way in SQL to tell an interested user his current rank in the table? I.e. given a user "id", sort all records by the "money" column and then let him know his rank. I'm sure I can implement this in Perl, but then this will have to be a cronjob, because I'll have to perform somewhat complex calculations for each user "id" and store them into another table. But a cronjob isn't nice, because it won't show the rank in "real time". Is there maybe an elegant and quick way for this in SQL? Thank you for any ideas Alex P.S.: Using postgresql-8.4.5-1PGDG.rhel5 with CentOS 5.5 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Sorting rows by a column and storing a row number
Hello, I have a card game for each I'd like to introduce weekly tournaments. I'm going to save the score (virtual money) won by each player into: create table pref_money ( id varchar(32) references pref_users, yw char(7) default to_char(current_timestamp, '-WW'), money real ); create index pref_money_yw_index on pref_money(yw); This way I don't have to perform any special calculations at the end of a week to find the weekly winner - just select all records for the current year-week, sort them by "money" column and take the 1st one. But I wonder, if there is a nice way in SQL to tell an interested user his current rank in the table? I.e. given a user "id", sort all records by the "money" column and then let him know his rank. I'm sure I can implement this in Perl, but then this will have to be a cronjob, because I'll have to perform somewhat complex calculations for each user "id" and store them into another table. But a cronjob isn't nice, because it won't show the rank in "real time". Is there maybe an elegant and quick way for this in SQL? Thank you for any ideas Alex P.S.: Using postgresql-8.4.5-1PGDG.rhel5 with CentOS 5.5 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] difference functions
This post is just to record an example of how to use the new window fn's in 8.4 to perform difference-between-row calculations. To demonstrate, we create a table with 30 rows of data, two columns, one of which contains the sequence 1..30, the other contains mod(c1,10). So the table looks like this: >\d x Table "public.x" Column | Type | Modifiers +-+--- c1 | integer | c2 | integer | > select * from x order by c1,c2; c1 | c2 + 1 | 1 2 | 2 3 | 3 4 | 4 5 | 5 6 | 6 7 | 7 8 | 8 9 | 9 10 | 0 11 | 1 12 | 2 13 | 3 14 | 4 15 | 5 16 | 6 17 | 7 18 | 8 19 | 9 20 | 0 21 | 1 22 | 2 23 | 3 24 | 4 25 | 5 26 | 6 27 | 7 28 | 8 29 | 9 30 | 0 (30 rows) Now if I want to calculate the difference between two rows, I can use the lag() window function (8.4 manual ch 9.19). The query and result set of a typical function would be: > select c1,c2,lag(c1,1,(select max(c1) from x x1 where x1.c2 = x.c2 and x1.c1 > < x.c1)) over ( partition by c2 order by c1) from x; c1 | c2 | lag ++- 10 | 0 | 20 | 0 | 10 30 | 0 | 20 1 | 1 | 11 | 1 | 1 21 | 1 | 11 2 | 2 | 12 | 2 | 2 22 | 2 | 12 3 | 3 | 13 | 3 | 3 23 | 3 | 13 4 | 4 | 14 | 4 | 4 24 | 4 | 14 5 | 5 | 15 | 5 | 5 25 | 5 | 15 6 | 6 | 16 | 6 | 6 26 | 6 | 16 7 | 7 | 17 | 7 | 7 27 | 7 | 17 8 | 8 | 18 | 8 | 8 28 | 8 | 18 9 | 9 | 19 | 9 | 9 29 | 9 | 19 (30 rows) Almost there. You can already see that the delta is (lag - c1). But: how to handle the missing-first-row-null-delta. Now, we need a 'sliding window' capability, so we can perform the calculations using values outside of this result set. (eg stock values have a daily closing price that would be used to calculate the next day's delta etc) (this is always messy, so I was really happy to discover that the lag function provides an elegant way of reducing the mess). In this example, let's assume that the first row in each partition (i.e. c1<=10) is our 'previous-row', upon which we will base our delta. So, handling the 'first-value' case is as follows: > select c1,c2,lag(c1,1,(select max(c1) from x x1 where x1.c2 = x.c2 and x1.c1 > < x.c1)) over ( partition by c2 order by c1) from x where c1 > 10; c1 | c2 | lag ++- 20 | 0 | 10 30 | 0 | 20 11 | 1 | 1 21 | 1 | 11 12 | 2 | 2 22 | 2 | 12 13 | 3 | 3 23 | 3 | 13 14 | 4 | 4 24 | 4 | 14 15 | 5 | 5 25 | 5 | 15 16 | 6 | 6 26 | 6 | 16 17 | 7 | 7 27 | 7 | 17 18 | 8 | 8 28 | 8 | 18 19 | 9 | 9 29 | 9 | 19 (20 rows) As you can see, now the 'lag' column contains the previous value of c1 in all cases, the first of which is calculated from the previous 'sliding-window' maximum. You can now add the delta calculation (lag-c1) and you have your delta. Finally, another point, on performance. The explain plan for this is: > explain select c1,c2,lag(c1,1,(select max(c1) from x x1 where x1.c2 = x.c2 > and x1.c1 < x.c1)) over ( partition by c2 order by c1) from x where c1 > 10; QUERY PLAN -- WindowAgg (cost=1.84..33.02 rows=21 width=8) -> Sort (cost=1.84..1.89 rows=21 width=8) Sort Key: x.c2, x.c1 -> Seq Scan on x (cost=0.00..1.38 rows=21 width=8) Filter: (c1 > 10) SubPlan 1 -> Aggregate (cost=1.45..1.46 rows=1 width=4) -> Seq Scan on x x1 (cost=0.00..1.45 rows=1 width=4) Filter: ((c1 < $1) AND (c2 = $0)) (9 rows) We have already gained because this feature eliminates what would have been handled by a lot of messy client code, not to mention the reduction of all of this to a single scan, and lookup for the first row. But the first row lookup is going to mean multiple-lookups, and if your table 'x' is large, that's going to hurt. However, typically, I will offset the first-row-lookup overhead by preparing for it: Create a small temp table containing exactly the first-row-lag-set that you are gonna want And then the first-row-query goes against the temp table. If the size of table 'x' is such that you need to do this, it'll help. Alternatively, instead of a sub-query, you can union the temp table into your calculation, and then exclude it from the final result using nested-sub-queries. Small temp tables are trivial to build and very useful for this type of thing. I used this technique to do a covariance matrix calculation in an oracle database a while ago - it kinda worked pretty well. So I'm really glad to see it available in pg now - it's really gonna help getting queries simpler and faster. Mr
Re: [GENERAL] max_fsm_pages increase
On Sun, Oct 31, 2010 at 2:43 AM, AI Rumman wrote: > I using Postgresql 8.1 and during vacuum at night time, I am getting the > following log: > number of page slots needed (2520048) exceeds max_fsm_pages (356656) > Do I need to increase max_fsm_pages to 2520048? Does it have any bad affect? No, you should set it to something higher most times. My production dbs, which are in the 100G range of size, and have a lot of updates, and very aggressive autovac setup, floats at around 2.5Million, and has max fsm set to 10M. It'll use a bit of shared mem (6 bytes per I think) so for me that's 60Meg of shared memory. Considering this machine has shared_buffers set to 8 Gig, that's not a whole lot of extra memory being used for me. Note two things: If your database is not getting vacuumed aggressively enough to keep up then you need to adjust autovacuum to keep up (more threads, less sleep, higher cost limits). Also, moving to 8.4 will get you out of this hole, as the free space map was moved from shared_memory to files on the hard drive. -- 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] Implementing replace function
On 31/10/2010 17:28, Alexander Farber wrote: I've created a function now (below) and can call it as well, but how can I see it at the psql prompt? Is there a \d command for that or should I dump the database to see my declarations? You can do \df public.*, assuming that your function is in the public schema, to get a list of all functions; and \df+ will give you the function body also. \? is your friend here. And is my function atomic? I.e. can't it happen, that FOUND is not true, but then another session calls a INSERT inbetween? Yes - all functions are executed in an implicit transaction. 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
Re: [GENERAL] Implementing replace function
\df *update* Alexander Farber wrote: I've created a function now (below) and can call it as well, but how can I see it at the psql prompt? Is there a \d command for that or should I dump the database to see my declarations? And is my function atomic? I.e. can't it happen, that FOUND is not true, but then another session calls a INSERT inbetween? pref=> create or replace function pref_update_users(_id varchar, pref(> _first_name varchar, _last_name varchar, _female boolean, pref(> _avatar varchar, _city varchar, _last_ip inet) returns void as $BODY$ pref$> BEGIN pref$> pref$> update pref_users set pref$> first_name = _first_name, pref$> last_name = _last_name, pref$> female = _female, pref$> avatar = _avatar, pref$> city = _city, pref$> last_ip = _last_ip pref$> where id = _id; pref$> pref$> IF NOT FOUND THEN pref$> insert into pref_users(id, first_name, pref$> last_name, female, avatar, city, last_ip) pref$> values (_id, _first_name, _last_name, pref$> _female, _avatar, _city, _last_ip); pref$> END IF; pref$> END; pref$> $BODY$ language plpgsql; CREATE FUNCTION Thanks and merry Halloween Alex -- 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] Implementing replace function
I've created a function now (below) and can call it as well, but how can I see it at the psql prompt? Is there a \d command for that or should I dump the database to see my declarations? And is my function atomic? I.e. can't it happen, that FOUND is not true, but then another session calls a INSERT inbetween? pref=> create or replace function pref_update_users(_id varchar, pref(> _first_name varchar, _last_name varchar, _female boolean, pref(> _avatar varchar, _city varchar, _last_ip inet) returns void as $BODY$ pref$> BEGIN pref$> pref$> update pref_users set pref$> first_name = _first_name, pref$> last_name = _last_name, pref$> female = _female, pref$> avatar = _avatar, pref$> city = _city, pref$> last_ip = _last_ip pref$> where id = _id; pref$> pref$> IF NOT FOUND THEN pref$> insert into pref_users(id, first_name, pref$> last_name, female, avatar, city, last_ip) pref$> values (_id, _first_name, _last_name, pref$> _female, _avatar, _city, _last_ip); pref$> END IF; pref$> END; pref$> $BODY$ language plpgsql; CREATE FUNCTION Thanks and merry Halloween Alex -- 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] NOT IN (NULL) ?
"Paul" writes: > But there is not such thing in PostgreSQL as empty set as "IN ()" that > must be > false, because nothing element may be found in empty set. > And I thought that instead of "IN ()" I could use "IN (NULL)", but I > was failed and result was NULL and not FALSE. :( NULL is not an alternative spelling for an empty set. You could get an empty IN set by using a sub-select yielding no rows, for example regression=# select 1 in (select 1 where false); ?column? -- f (1 row) regression=# select 1 not in (select 1 where false); ?column? -- t (1 row) 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] NOT IN (NULL) ?
2010/10/31 Paul : > Tom, > > Sunday, October 31, 2010, 9:42:27 PM, you wrote: > > TL> Because the SQL standard says so. > > But there is not such thing in PostgreSQL as empty set as "IN ()" that > must be > false, because nothing element may be found in empty set. > And I thought that instead of "IN ()" I could use "IN (NULL)", but I > was failed and result was NULL and not FALSE. :( > (NULL) isn't empty set. Empty set can be (SELECT 1 WHERE false) Regards Pavel > -- > Paul > > > -- > 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] NOT IN (NULL) ?
Tom, Sunday, October 31, 2010, 9:42:27 PM, you wrote: TL> Because the SQL standard says so. But there is not such thing in PostgreSQL as empty set as "IN ()" that must be false, because nothing element may be found in empty set. And I thought that instead of "IN ()" I could use "IN (NULL)", but I was failed and result was NULL and not FALSE. :( -- Paul -- 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] NOT IN (NULL) ?
On 31/10/2010 16:37, Paul wrote: Please, help me. Why the condition SELECT 5 NOT IN (NULL) returns NULL, but not FALSE (as I thought)? Because NULL basically means "don't know" - so you don't know whether 5 is there or not. 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
Re: [GENERAL] NOT IN (NULL) ?
"Paul" writes: > Why the condition > SELECT 5 NOT IN (NULL) > returns NULL, but not FALSE (as I thought)? Because the SQL standard says so. If you think of NULL as meaning "unknown", it makes some intuitive sense: it's unknown whether that unknown value is equal to 5. 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] NOT IN (NULL) ?
Please, help me. Why the condition SELECT 5 NOT IN (NULL) returns NULL, but not FALSE (as I thought)? -- Paul -- 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] large xml database
On 10/30/2010 4:48 PM, Viktor Bojović wrote: Hi, i have very big XML documment which is larger than 50GB and want to import it into databse, and transform it to relational schema. When splitting this documment to smaller independent xml documments i get ~11.1mil XML documents. I have spent lots of time trying to get fastest way to transform all this data but every time i give up because it takes too much time. Sometimes more than month it would take if not stopped. I have tried to insert each line as varchar into database and parse it using plperl regex.. also i have tried to store every documment as XML and parse it, but it is also to slow. i have tried to store every documment as varchar but it is also slow when using regex to get data. many tries have failed because 8GB of ram and 10gb of swap were not enough. also sometimes i get that more than 2^32 operations were performed, and functions stopped to work. i wanted just to ask if someone knows how to speed this up. thanx in advance -- --- Viktor Bojović --- Wherever I go, Murphy goes with me It might help a little to know a few more detail. Like what is in the xml doc. When you say convert to relational, do you mean multiple tables (and no more xml tags), or do you mean a table with a blob column that contains some xml fragment? I have imported millions of rows and never run out of memory. The database will take care of itself unless you are doing something really bad. I'd guess its the xml parser running out of ram and not the database. Are you using dom or sax? You say it took too much time. What did? The xml parsing? The database inserts? Were you cpu bound or io bound? What tools are you using to write this in? What OS are you on? What version of PG? You know... just a "few" more details :-) -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: [GENERAL] Implementing replace function
2010/10/31 Alexander Farber : > And would a pure SQL-function solution to call > an INSERT followed by an UPDATE in its body > and ignoring the error? (don't know how ignore it > best though, so that I don't ignore other critical errors) > You must not ignore errors in SQL - it's not T-SQL :) Pavel > -- > 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] Implementing replace function
2010/10/31 Alexander Farber : > Thanks for all the comments. > > Do I need to use BEGIN/COMMIT in my plpgsql-function for UPSERT or are > functions atomic? If you use a code from documentation, then you don't need explicit transaction - every SQL run inside outer implicit transaction in PostgreSQL. Pavel > > Regards > Alex > > -- > 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] Implementing replace function
[corrected typo, sorry] And wouldn't a pure SQL-function solution be: to call an INSERT followed by an UPDATE in its body and ignoring the error? (don't know how ignore that error best though, so that I don't ignore other critical errors) -- 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] Implementing replace function
And would a pure SQL-function solution to call an INSERT followed by an UPDATE in its body and ignoring the error? (don't know how ignore it best though, so that I don't ignore other critical errors) -- 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] Implementing replace function
Thanks for all the comments. Do I need to use BEGIN/COMMIT in my plpgsql-function for UPSERT or are functions atomic? Regards Alex -- 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] Implementing replace function
Alexander Farber wrote on 31.10.2010 09:22: Hello Postgres users, to mimic the MySQL-REPLACE statement I need to try to UPDATE a record and if that fails - INSERT it. There is actually an example of this in the PG manual ;) http://www.postgresql.org/docs/current/static/plpgsql-control-structures.html#PLPGSQL-UPSERT-EXAMPLE Here is another solution based on triggers: http://database-programmer.blogspot.com/2009/06/approaches-to-upsert.html 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] Implementing replace function
2010/10/31 Dmitriy Igrishin : > Okay, Pavel, will wait for 9.1 :-) > > It is a common case - insert new row if it cannot be updated. you can find (probably) MERGE statement in 9.1. Pavel > > 2010/10/31 Pavel Stehule >> >> Hello >> >> 2010/10/31 Dmitriy Igrishin : >> > Hey Alexander, Pavel >> > >> > The solution like below should works IMO, but it does not. >> > insert into pref_users(id, first_name, last_name, >> > female, avatar, city, last_ip) >> > select $1, $2, $3, $4, $5, $6, $7 >> > where not exists >> > (update pref_users set first_name = $2, >> > last_name = $3, >> > female = $4, >> > avatar = $5, >> > city = $6, >> > last_ip = $7 >> > where id = $1 >> > returning id); >> > >> > BTW, I don't understand why it not possible to write query like this: >> > SELECT id FROM (UPDATE test SET nm = 'dima' WHERE id = 1 RETURNING id) >> > AS >> > foo; >> > According to the doc (of UPDATE command) "The syntax of the RETURNING >> > list >> > is identical to >> > that of the output list of SELECT). >> > With this syntax, the OPs goal can be implemented in SQL.. >> > >> >> UPDATE RETURNING isn't subselect - so you can't do SELECT FROM (UPDATE >> RETURNING) directly. It's possible with wrapping to sql function. >> >> In next pg version 9.1 you can do it via Updatable Common Table >> Expression, but it isn't possible in older version. >> >> Regards >> >> Pavel Stehule >> >> > -- >> > // Dmitriy. >> > >> > >> > > > > > -- > // Dmitriy. > > > -- 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] Implementing replace function
Okay, Pavel, will wait for 9.1 :-) It is a common case - insert new row if it cannot be updated. 2010/10/31 Pavel Stehule > Hello > > 2010/10/31 Dmitriy Igrishin : > > Hey Alexander, Pavel > > > > The solution like below should works IMO, but it does not. > > insert into pref_users(id, first_name, last_name, > > female, avatar, city, last_ip) > > select $1, $2, $3, $4, $5, $6, $7 > > where not exists > > (update pref_users set first_name = $2, > > last_name = $3, > > female = $4, > > avatar = $5, > > city = $6, > > last_ip = $7 > > where id = $1 > > returning id); > > > > BTW, I don't understand why it not possible to write query like this: > > SELECT id FROM (UPDATE test SET nm = 'dima' WHERE id = 1 RETURNING id) AS > > foo; > > According to the doc (of UPDATE command) "The syntax of the RETURNING > list > > is identical to > > that of the output list of SELECT). > > With this syntax, the OPs goal can be implemented in SQL.. > > > > UPDATE RETURNING isn't subselect - so you can't do SELECT FROM (UPDATE > RETURNING) directly. It's possible with wrapping to sql function. > > In next pg version 9.1 you can do it via Updatable Common Table > Expression, but it isn't possible in older version. > > Regards > > Pavel Stehule > > > -- > > // Dmitriy. > > > > > > > -- // Dmitriy.
Re: [GENERAL] Implementing replace function
Hello 2010/10/31 Dmitriy Igrishin : > Hey Alexander, Pavel > > The solution like below should works IMO, but it does not. > insert into pref_users(id, first_name, last_name, > female, avatar, city, last_ip) > select $1, $2, $3, $4, $5, $6, $7 > where not exists > (update pref_users set first_name = $2, > last_name = $3, > female = $4, > avatar = $5, > city = $6, > last_ip = $7 > where id = $1 > returning id); > > BTW, I don't understand why it not possible to write query like this: > SELECT id FROM (UPDATE test SET nm = 'dima' WHERE id = 1 RETURNING id) AS > foo; > According to the doc (of UPDATE command) "The syntax of the RETURNING list > is identical to > that of the output list of SELECT). > With this syntax, the OPs goal can be implemented in SQL.. > UPDATE RETURNING isn't subselect - so you can't do SELECT FROM (UPDATE RETURNING) directly. It's possible with wrapping to sql function. In next pg version 9.1 you can do it via Updatable Common Table Expression, but it isn't possible in older version. Regards Pavel Stehule > -- > // Dmitriy. > > > -- 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] Implementing replace function
Hey Alexander, Pavel The solution like below should works IMO, but it does not. insert into pref_users(id, first_name, last_name, female, avatar, city, last_ip) select $1, $2, $3, $4, $5, $6, $7 where not exists (update pref_users set first_name = $2, last_name = $3, female = $4, avatar = $5, city = $6, last_ip = $7 where id = $1 returning id); BTW, I don't understand why it not possible to write query like this: SELECT id FROM (UPDATE test SET nm = 'dima' WHERE id = 1 RETURNING id) AS foo; According to the doc (of UPDATE command) "The syntax of the RETURNING list is identical to that of the output list of SELECT). With this syntax, the OPs goal can be implemented in SQL.. -- // Dmitriy.
Re: [GENERAL] Implementing replace function
2010/10/31 Alexander Farber : > Thanks Pavel, but I have an SQL procedure and not plpgsql? it's not possible in sql. But plpgsql is same level like stored procedures language from mysql. > > On Sun, Oct 31, 2010 at 9:34 AM, Pavel Stehule > wrote: >>> But how can I detect that the UPDATE has failed in my SQL procedure? >>> >> >> see: >> http://www.postgresql.org/docs/8.4/interactive/plpgsql-control-structures.html >> near to end of page > >>> create or replace function update_pref_users(id varchar, >>> first_name varchar, last_name varchar, female boolean, >>> avatar varchar, city varchar, last_ip inet) returns void as $$ >>> >>> update pref_users set >>> first_name = $2, >>> last_name = $3, >>> female = $4, >>> avatar = $5, >>> city = $6, >>> last_ip = $7 >>> where id = $1; >>> >>> -- XXX how to detect failure here? XXX >>> >>> insert into pref_users(id, first_name, last_name, >>> female, avatar, city, last_ip) >>> values ($1, $2, $3, $4, $5, $6, $7); >>> $$ language sql; > > -- > 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] Implementing replace function
Thanks Pavel, but I have an SQL procedure and not plpgsql? On Sun, Oct 31, 2010 at 9:34 AM, Pavel Stehule wrote: >> But how can I detect that the UPDATE has failed in my SQL procedure? >> > > see: > http://www.postgresql.org/docs/8.4/interactive/plpgsql-control-structures.html > near to end of page >> create or replace function update_pref_users(id varchar, >> first_name varchar, last_name varchar, female boolean, >> avatar varchar, city varchar, last_ip inet) returns void as $$ >> >> update pref_users set >> first_name = $2, >> last_name = $3, >> female = $4, >> avatar = $5, >> city = $6, >> last_ip = $7 >> where id = $1; >> >> -- XXX how to detect failure here? XXX >> >> insert into pref_users(id, first_name, last_name, >> female, avatar, city, last_ip) >> values ($1, $2, $3, $4, $5, $6, $7); >> $$ language sql; -- 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] max_fsm_pages increase
Hello 2010/10/31 AI Rumman : > I using Postgresql 8.1 and during vacuum at night time, I am getting the > following log: > number of page slots needed (2520048) exceeds max_fsm_pages (356656) > Do I need to increase max_fsm_pages to 2520048? Does it have any bad affect? I takes a little more memory, but if you can run VACUUM FULL, then do it. It reduce a free space inside data files and it reduce a requests on max_fsm_pages. Regards Pavel Stehule -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] max_fsm_pages increase
I using Postgresql 8.1 and during vacuum at night time, I am getting the following log: number of page slots needed (2520048) exceeds max_fsm_pages (356656) Do I need to increase max_fsm_pages to 2520048? Does it have any bad affect?
Re: [GENERAL] Implementing replace function
Hello 2010/10/31 Alexander Farber : > Hello Postgres users, > > to mimic the MySQL-REPLACE statement I need > to try to UPDATE a record and if that fails - INSERT it. > > But how can I detect that the UPDATE has failed in my SQL procedure? > see: http://www.postgresql.org/docs/8.4/interactive/plpgsql-control-structures.html near to end of page Regards Pavel Stehule > begin transaction; > > create table pref_users ( > id varchar(32) primary key, > first_name varchar(32), > last_name varchar(32), > female boolean, > avatar varchar(128), > city varchar(32), > lat real check (-90 <= lat and lat <= 90), > lng real check (-90 <= lng and lng <= 90), > last_login timestamp default current_timestamp, > last_ip inet, > medals smallint check (medals > 0) > ); > > create table pref_rate ( > obj varchar(32) references pref_users(id), > subj varchar(32) references pref_users(id), > good boolean, > fair boolean, > nice boolean, > about varchar(256), > last_rated timestamp default current_timestamp > ); > > create table pref_money ( > id varchar(32) references pref_users, > yw char(7) default to_char(current_timestamp, '-WW'), > money real > ); > create index pref_money_yw_index on pref_money(yw); > > create or replace function update_pref_users(id varchar, > first_name varchar, last_name varchar, female boolean, > avatar varchar, city varchar, last_ip inet) returns void as $$ > > update pref_users set > first_name = $2, > last_name = $3, > female = $4, > avatar = $5, > city = $6, > last_ip = $7 > where id = $1; > > -- XXX how to detect failure here? XXX > > insert into pref_users(id, first_name, last_name, > female, avatar, city, last_ip) > values ($1, $2, $3, $4, $5, $6, $7); > $$ language sql; > > commit; > > Thank you > Alex > > -- > 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] Implementing replace function
Hello Postgres users, to mimic the MySQL-REPLACE statement I need to try to UPDATE a record and if that fails - INSERT it. But how can I detect that the UPDATE has failed in my SQL procedure? begin transaction; create table pref_users ( id varchar(32) primary key, first_name varchar(32), last_name varchar(32), female boolean, avatar varchar(128), city varchar(32), lat real check (-90 <= lat and lat <= 90), lng real check (-90 <= lng and lng <= 90), last_login timestamp default current_timestamp, last_ip inet, medals smallint check (medals > 0) ); create table pref_rate ( obj varchar(32) references pref_users(id), subj varchar(32) references pref_users(id), good boolean, fair boolean, nice boolean, about varchar(256), last_rated timestamp default current_timestamp ); create table pref_money ( id varchar(32) references pref_users, yw char(7) default to_char(current_timestamp, '-WW'), money real ); create index pref_money_yw_index on pref_money(yw); create or replace function update_pref_users(id varchar, first_name varchar, last_name varchar, female boolean, avatar varchar, city varchar, last_ip inet) returns void as $$ update pref_users set first_name = $2, last_name = $3, female = $4, avatar = $5, city = $6, last_ip = $7 where id = $1; -- XXX how to detect failure here? XXX insert into pref_users(id, first_name, last_name, female, avatar, city, last_ip) values ($1, $2, $3, $4, $5, $6, $7); $$ language sql; commit; Thank you Alex -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
