Re: [GENERAL] calling a function that takes a row type and returns a set of rows
2008/10/10 Dimitri Fontaine <[EMAIL PROTECTED]>: > -BEGIN PGP SIGNED MESSAGE- > Hash: SHA1 > > Hi, > > Le 10 oct. 08 à 21:22, Robert Haas a écrit : >> >> I can't find any legal way of calling this function. >> >> SELECT bar(f) FROM foo f; >> ERROR: set-valued function called in context that cannot accept a set >> >> SELECT * FROM foo f, bar(f); >> ERROR: function expression in FROM may not refer to other relations >> of same query level >> >> Any help appreciated. > > > You need LATERAL support for this: > SELECT * FROM foo f LATERAL bar(f); > > I'm not sure about the syntax, but LATERAL is a standard JOIN type wherein > upper "nodes" are visible. > - -- > dim > no, this strange syntax is far to any standard. Solution is using dynamic cursor ala DB2 (that isn't supported in postgres) - select * from fce(cursor(select from tab)) Regards Pavel Stehule > -BEGIN PGP SIGNATURE- > Version: GnuPG v1.4.9 (Darwin) > > iEYEARECAAYFAkjvvYMACgkQlBXRlnbh1blatgCgnaDoSY2RGzv224QWqA8OYEjx > fbMAoK31dHoFjOVRdomvhl/qilndRZJ5 > =3xjL > -END PGP SIGNATURE- > > -- > 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 remove the duplicate records from a table
On Tuesday 07 October 2008 05:48:01 Albe Laurenz wrote:
> Yi Zhao wrote:
> > I have a table contains some duplicate records, and this table create
> > without oids, for example:
> > id | temp_id
> > +-
> > 10 | 1
> > 10 | 1
> > 10 | 1
> > 20 | 4
> > 20 | 4
> > 30 | 5
> > 30 | 5
> > I want get the duplicated records removed and only one is reserved, so
> > the results is:
> > 10 1
> > 20 4
> > 30 5
> >
> > I know create a temp table will resolve this problem, but I don't want
> > this way:)
> >
> > can someone tell me a simple methold?
>
> Don't know if you'd call that simple, but if the table is
> called "t", you could do
>
> DELETE FROM t t1 USING t t2
> WHERE t1.id = t2.id AND t1.temp_id = t2.temp_id AND t1.ctid > t2.ctid;
>
note that one problem the delete from approaches have that the temp table
solutions dont is that you can end up with a lot of dead tuples if there were
a lot of duplicates... so if you can afford the locks, its not a bad idea to
do begin; lock table t1 in access exclsuive mode; create temp table x as
select ... from t1; truncate t1; insert into t1 select * from x; create
unique index ui1 on t1(...); commit; this way you're now unique table will
be nice and compacted, and wont get any more duplicate rows.
--
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL
--
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] Re: [Pkg-postgresql-public] Postgres major version support policy on Debian
On Fri, Oct 10, 2008 at 2:23 AM, Gerfried Fuchs <[EMAIL PROTECTED]> wrote: > * Scott Marlowe <[EMAIL PROTECTED]> [2008-10-06 18:07:39 CEST]: >> On Mon, Oct 6, 2008 at 9:34 AM, Markus Wanner <[EMAIL PROTECTED]> wrote: >> > Well, it's a general Postgres problem, not a Debian one. Upgrading >> > between major versions requires a full dump/restore cycle, for which the >> > downtime is proportional to the database size. For small or medium >> > databases that's not an issue, but above some Gigabytes, that begins to >> > hurt pretty badly. >> >> In that case I prefer to have both db versions available and use slony >> to upgrade in place. We recently upgraded from 8.1 to 8.3 and work >> the downtime was measured in seconds (the time it took slony to switch >> the two servers). > > Good to hear. Though I see another problem here, slony is always only > available for a single postgres version in the current packaging, so > that upgrading path isn't that easy as you make it sound... At least not > if you want to do it on a single system and not through two different > machines. This is certainly not true for slony on ubuntu. On Ubuntu there's a slony1-bin package that has the common files, and then there's postgresql-8.x-slony1 package for each pgsql version that has the scripts to make that version happy. -- 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] calling a function that takes a row type and returns a set of rows
> You need LATERAL support for this: > SELECT * FROM foo f LATERAL bar(f); > > I'm not sure about the syntax, but LATERAL is a standard JOIN type wherein > upper "nodes" are visible. That would be really nice. Then you could presumably also do: SELECT f.id, f.name, f.apple, f.banana, bar.apple AS bar_apple, bar.banana AS bar_banana FROM foo f LATERAL bar(f); ...which I frequently wish to do, and can't. ...Robert -- 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] Bug In COPY TO?
Bill Thoen <[EMAIL PROTECTED]> writes: > I noticed that in PG 8.1 the docs for COPY TO say that you can use WITH > HEADER, but when I do that it throws an error saying that this works > only in CSV mode. Is this a bug or do I have something wrong with my syntax? I'd say it's a bug in the documentation :-(. It looks like someone carelessly added HEADER in the wrong place in the syntax summary. 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] calling a function that takes a row type and returns a set of rows
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi, Le 10 oct. 08 à 21:22, Robert Haas a écrit : I can't find any legal way of calling this function. SELECT bar(f) FROM foo f; ERROR: set-valued function called in context that cannot accept a set SELECT * FROM foo f, bar(f); ERROR: function expression in FROM may not refer to other relations of same query level Any help appreciated. You need LATERAL support for this: SELECT * FROM foo f LATERAL bar(f); I'm not sure about the syntax, but LATERAL is a standard JOIN type wherein upper "nodes" are visible. - -- dim -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.9 (Darwin) iEYEARECAAYFAkjvvYMACgkQlBXRlnbh1blatgCgnaDoSY2RGzv224QWqA8OYEjx fbMAoK31dHoFjOVRdomvhl/qilndRZJ5 =3xjL -END PGP SIGNATURE- -- 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] calling a function that takes a row type and returns a set of rows
Hmm, the implicit cursor approach approach won't work for me because I
want to be able to call the function on an arbitrary slice of the rows
in the table, but the explicit cursor approach looks like it might
work. I'll give that a try, thanks.
...Robert
On Fri, Oct 10, 2008 at 4:01 PM, Pavel Stehule <[EMAIL PROTECTED]> wrote:
> Hello
>
> PostgreSQL doesn't support pipe functions, so you cannot do what you
> wont. But you should to use SQL SETOF functions, that should be
> called in normal context. I dislike this feature, but it should be
> useful for you,
>
> try:
>
> create or replace function bar1(foo)
> returns setof foo as $$
> select 1, $1.b
> union all
> select 2, $1.b;
> $$ language sql;
>
> postgres=# select (bar1(foo)).* from foo;
> a | b
> ---+---
> 1 | 1
> 2 | 1
> (2 rows)
>
> I thing, so much better and cleaner version is using explicit or
> implicit cursor in function
>
> -- implicit cursor
> create or replace function bar() returns setof foo as $$
> declare r record;
> begin
> for r in select * from foo loop
>r.a := 1;
>return next r;
>r.a := 2;
>return next r;
> end loop;
> return;
> end;
> $$ language plpgsql;
>
> postgres=# select * from bar();
> a | b
> ---+---
> 1 | 1
> 2 | 1
> (2 rows)
>
> -- using explicit cursor (it's more complicated variant, and I thing,
> so it's better don't use it)
> create or replace function bar(c refcursor) returns setof foo as $$
> declare r record;
> begin
> loop
>fetch c into r;
>exit when not found;
>r.a := 1;
>return next r;
>r.a := 2;
>return next r;
> end loop;
> return;
> end;
> $$ language plpgsql;
>
> begin;
> declare x cursor for select * from foo;
> select * from bar('x'::refcursor);
> commit;
>
> postgres=# declare x cursor for select * from foo;
> DECLARE CURSOR
> postgres=# select * from bar('x'::refcursor);
> a | b
> ---+---
> 1 | 1
> 2 | 1
> (2 rows)
>
> postgres=# commit;
> COMMIT
>
> Regards
> Pavel Stehule
>
>
> 2008/10/10 Robert Haas <[EMAIL PROTECTED]>:
>> So, say I have something like this - the actual example is something a
>> bit more useful:
>>
>> CREATE TABLE foo (a integer, b integer);
>> INSERT INTO foo VALUES (1, 1); -- must have some data to generate the
>> failure
>>
>> CREATE FUNCTION bar (foo) RETURNS SETOF foo AS $$
>> DECLARE
>>f foo;
>> BEGIN
>>f.a := 1;
>>RETURN NEXT f;
>>f.a := 2;
>>RETURN NEXT f;
>> END
>> $$ LANGUAGE plpgsql;
>>
>> I can't find any legal way of calling this function.
>>
>> SELECT bar(f) FROM foo f;
>> ERROR: set-valued function called in context that cannot accept a set
>>
>> SELECT * FROM foo f, bar(f);
>> ERROR: function expression in FROM may not refer to other relations
>> of same query level
>>
>> Any help appreciated.
>>
>> Thanks,
>>
>> ...Robert
>>
>> --
>> 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] calling a function that takes a row type and returns a set of rows
Hello
PostgreSQL doesn't support pipe functions, so you cannot do what you
wont. But you should to use SQL SETOF functions, that should be
called in normal context. I dislike this feature, but it should be
useful for you,
try:
create or replace function bar1(foo)
returns setof foo as $$
select 1, $1.b
union all
select 2, $1.b;
$$ language sql;
postgres=# select (bar1(foo)).* from foo;
a | b
---+---
1 | 1
2 | 1
(2 rows)
I thing, so much better and cleaner version is using explicit or
implicit cursor in function
-- implicit cursor
create or replace function bar() returns setof foo as $$
declare r record;
begin
for r in select * from foo loop
r.a := 1;
return next r;
r.a := 2;
return next r;
end loop;
return;
end;
$$ language plpgsql;
postgres=# select * from bar();
a | b
---+---
1 | 1
2 | 1
(2 rows)
-- using explicit cursor (it's more complicated variant, and I thing,
so it's better don't use it)
create or replace function bar(c refcursor) returns setof foo as $$
declare r record;
begin
loop
fetch c into r;
exit when not found;
r.a := 1;
return next r;
r.a := 2;
return next r;
end loop;
return;
end;
$$ language plpgsql;
begin;
declare x cursor for select * from foo;
select * from bar('x'::refcursor);
commit;
postgres=# declare x cursor for select * from foo;
DECLARE CURSOR
postgres=# select * from bar('x'::refcursor);
a | b
---+---
1 | 1
2 | 1
(2 rows)
postgres=# commit;
COMMIT
Regards
Pavel Stehule
2008/10/10 Robert Haas <[EMAIL PROTECTED]>:
> So, say I have something like this - the actual example is something a
> bit more useful:
>
> CREATE TABLE foo (a integer, b integer);
> INSERT INTO foo VALUES (1, 1); -- must have some data to generate the
> failure
>
> CREATE FUNCTION bar (foo) RETURNS SETOF foo AS $$
> DECLARE
>f foo;
> BEGIN
>f.a := 1;
>RETURN NEXT f;
>f.a := 2;
>RETURN NEXT f;
> END
> $$ LANGUAGE plpgsql;
>
> I can't find any legal way of calling this function.
>
> SELECT bar(f) FROM foo f;
> ERROR: set-valued function called in context that cannot accept a set
>
> SELECT * FROM foo f, bar(f);
> ERROR: function expression in FROM may not refer to other relations
> of same query level
>
> Any help appreciated.
>
> Thanks,
>
> ...Robert
>
> --
> 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] calling a function that takes a row type and returns a set of rows
So, say I have something like this - the actual example is something a bit more useful: CREATE TABLE foo (a integer, b integer); INSERT INTO foo VALUES (1, 1); -- must have some data to generate the failure CREATE FUNCTION bar (foo) RETURNS SETOF foo AS $$ DECLARE f foo; BEGIN f.a := 1; RETURN NEXT f; f.a := 2; RETURN NEXT f; END $$ LANGUAGE plpgsql; I can't find any legal way of calling this function. SELECT bar(f) FROM foo f; ERROR: set-valued function called in context that cannot accept a set SELECT * FROM foo f, bar(f); ERROR: function expression in FROM may not refer to other relations of same query level Any help appreciated. Thanks, ...Robert -- 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] Opteron vs. Xeon performance differences
When i question about WAL, i mean if WAL is in other drive. You must run a benchmark more expensive to cpu for make a conclusion. Make a query that have more of 8 seconds, then you can see really if exists a diference in other way... i think you don't use the same image of the old server in the new. In that way could be a configuration kernel. do you make a test of hardware instead postgres?? if the hard give you better numbers, so postgres have the problem. 2008/10/10 Shane Ambler <[EMAIL PROTECTED]>: > Bart Grantham wrote: >> >> a long story short: we're experiencing Xeons as 50% slower than >> Opterons, even when the Xeon has twice as much cache and a slight >> clock speed advantage. > >> tests I finally took the final leap: just pull the disks and throw >> them in a newer Opteron chassis (2.8GHz, 1M cache). And whaddya >> know? It's got a 20% speed edge on the older Opteron, and blows away >> the performance of the newer Xeons. > > But is the difference in cpu or disk? > > Do the two machines get a similar disk transfer rate? > > Same raid card and disks in both machines, do they get the same MB/Sec? > (as opposed to on-board controllers) > > > > -- > > Shane Ambler > pgSQL (at) Sheeky (dot) Biz > > Get Sheeky @ http://Sheeky.Biz > > -- > 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] Opteron vs. Xeon performance differences
Bart Grantham wrote: a long story short: we're experiencing Xeons as 50% slower than Opterons, even when the Xeon has twice as much cache and a slight clock speed advantage. tests I finally took the final leap: just pull the disks and throw them in a newer Opteron chassis (2.8GHz, 1M cache). And whaddya know? It's got a 20% speed edge on the older Opteron, and blows away the performance of the newer Xeons. But is the difference in cpu or disk? Do the two machines get a similar disk transfer rate? Same raid card and disks in both machines, do they get the same MB/Sec? (as opposed to on-board controllers) -- Shane Ambler pgSQL (at) Sheeky (dot) Biz Get Sheeky @ http://Sheeky.Biz -- 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] Opteron vs. Xeon performance differences
On Thu, 9 Oct 2008, Bart Grantham wrote: The full story: we have an older production server with 2G of RAM, 2.4GHz Opterons w/ 1M of cache...The newer servers have 4G of RAM, 3.0GHz Xeons with 2M of cache. Model numbers please? I can probably guess for the Opterons, there are a lot of different implementations lumped under the Xeon brand name. Have you taken compared how fast the RAM is in the two systems? We were just talking about a similar unexpected performance different yesterday on another list: http://archives.postgresql.org/pgsql-performance/2008-10/msg00051.php I'd be curious what memtest86+ and the simple hdparm -T benchmark say about the two servers. If those numbers correlate with the performance difference you're seeing, the PostgreSQL code might have nothing to do with it. I've seen a 60% performance difference just between the best and worst RAM I tried on a single motherboard recently. -- * Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD -- 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] Opteron vs. Xeon performance differences
Bart Grantham wrote: Forgive me if this has been beaten into the ground, but my team and I couldn’t find much conclusive study or posts on this issue. To make a long story short: we’re experiencing Xeons as 50% slower than Opterons, even when the Xeon has twice as much cache and a slight clock speed advantage. Simple question, you know that the plans are the same? And I don't think you said conclusively that it's the same version of PGSQL on both servers? -- 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] grabbing date of last Sunday?
On Oct 10, 2008, at 11:36 , Raymond O'Donnell wrote:
On 10/10/2008 16:29, blackwater dev wrote:
How can I grab the date from the last Sunday based on when I run
the query?
select
current_date
- (extract(dow from current_date) || ' days')::interval;
Concatenations in math always make me shudder (and the above will give
you a timestamp besides):
SELECT CURRENT_DATE,
CURRENT_DATE - CAST(EXTRACT(DOW FROM CURRENT_DATE) as int) AS
date_integer_arithmetic,
CAST(CURRENT_DATE - (EXTRACT(DOW FROM CURRENT_DATE) * INTERVAL
'1 DAY') AS DATE) AS date_interval_arithmetic,
CAST(date_trunc('week', CURRENT_DATE) AS DATE) - 1 AS
non_standard;
date| date_integer_arithmetic | date_interval_arithmetic |
non_standard
+-+--
+--
2008-10-10 | 2008-10-05 | 2008-10-05 |
2008-10-05
(1 row)
Michael Glaesemann
grzm seespotcode net
--
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] Improve dump and restore time
Hi, Pascal Cohen a écrit : > I am studying how to migrate our Production Database which is running > under PG 8.2 and we would like to move to PG 8.3 > I have read that the only safe solution is to perform a dump and restore. > Our DB is around 6GB large. > I wanted to have an expectation of the migration duration and performed > it on a less powerful machine than the one we have in Production. > Unfortunately it took very very long time (around 27 hours!). 27 hours feels really strange for only 6GB. I'm sure there's something wrong here. General advices. If you have big indexes, you should probably grow maintenance_work_mem. You should also take a look at your checkpoint_segments settings. > Are there best practices to reduce the migration time ? Using Slony is one. I'm not sure I would go this way for a 6GB database. But it would assure you a really small downtime. > Some kind of questions I try to solve: > - Is it better to use a compressed dump or not ? You'll loose time doing the compressed dump. > - Should I use a kind of trick like reading dump file from network while > restoring on the machine to reduce concurrent I/O and so on I would find this a better way than the compressed dump. Regards. -- Guillaume. http://www.postgresqlfr.org http://dalibo.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Improve dump and restore time
Thanks, in fact I also expected a couple of hours and I was surprised by this result. I will have a look to the hardware (unfortunately not before next week now). Thanks again. Rasper, Franz wrote: Hi, Normally it should be done in between 1 and 4 hours. Fastest version is maybe pg_dump ... | psql ... 27 hours is in my opinion a little bit to much. What is your hardware ? Greetings, -Franz -Ursprüngliche Nachricht- Von: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Im Auftrag von Pascal Cohen Gesendet: Freitag, 10. Oktober 2008 17:17 An: pgsql-general@postgresql.org Betreff: [Spam] [GENERAL] Improve dump and restore time Hello, I am studying how to migrate our Production Database which is running under PG 8.2 and we would like to move to PG 8.3 I have read that the only safe solution is to perform a dump and restore. Our DB is around 6GB large. I wanted to have an expectation of the migration duration and performed it on a less powerful machine than the one we have in Production. Unfortunately it took very very long time (around 27 hours!). Are there best practices to reduce the migration time ? Some kind of questions I try to solve: - Is it better to use a compressed dump or not ? - Should I use a kind of trick like reading dump file from network while restoring on the machine to reduce concurrent I/O and so on Any advice is welcome. Thanks! -- 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] grabbing date of last Sunday?
On 10/10/2008 16:29, blackwater dev wrote: > How can I grab the date from the last Sunday based on when I run the query? select current_date - (extract(dow from current_date) || ' days')::interval; :-) Ray. -- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland [EMAIL PROTECTED] Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals -- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] grabbing date of last Sunday?
How can I grab the date from the last Sunday based on when I run the query? For example I run it today, and I need to date of 10-5-08, if I ran it next week, I would want 10-12-08, etc. Thanks!
[GENERAL] Bug In COPY TO?
I noticed that in PG 8.1 the docs for COPY TO say that you can use WITH HEADER, but when I do that it throws an error saying that this works only in CSV mode. Is this a bug or do I have something wrong with my syntax? COPY mytable TO '/data/out.txt' WITH HEADER DELIMITER '|' NULL '' ; -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Improve dump and restore time
Hello, I am studying how to migrate our Production Database which is running under PG 8.2 and we would like to move to PG 8.3 I have read that the only safe solution is to perform a dump and restore. Our DB is around 6GB large. I wanted to have an expectation of the migration duration and performed it on a less powerful machine than the one we have in Production. Unfortunately it took very very long time (around 27 hours!). Are there best practices to reduce the migration time ? Some kind of questions I try to solve: - Is it better to use a compressed dump or not ? - Should I use a kind of trick like reading dump file from network while restoring on the machine to reduce concurrent I/O and so on Any advice is welcome. 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] Using subquery or creating temp table
temporary tables make sens, if you want to operate on multiple queries in the same connection.Also, temporary tables are visible only to the connection, if multiple connections will create temp table by the same name - they all will see their own content, ie - it is not shared between connections. What you probably need, is either wait for 8.4, and use WITH() , or use subquery as 'temporary' data. or fire two queries, one creating temporary table, second one using it. Try all, and see which one will apply best to your problem, and which one will be fastest. I am using temporary tables, to keep large amounts of data, that's used for transactions within connection. That saves me bandwith, all in all - time, and makes life much easier.
Re: [GENERAL] left join conditon causes error
2008/10/8 Andrus <[EMAIL PROTECTED]>: > Query Query deleted for brevity > ERROR: invalid reference to FROM-clause entry for table "destkonto" > LINE 7: ... =sihrkurs.kuupaev AND sihrkurs.raha=destkonto >^ > HINT: There is an entry for table "destkonto", but it cannot be referenced > from this part of the query. It's a question of precedence. select * from a,b join c ... is execute in the order of b join c then a join that result. By the way, this is how the sql spec says to do it. So, you can't join b to c based on anything from a, because a hasn't been joined yet. I would recommend using explicit join syntax only and see how that works for 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] Opteron vs. Xeon performance differences
How do you manage the wal in both servers? The version kernel is the same in both? Runs the same services? Do you make some test with Posgresql only in both servers? If the problem is the inter-CPU, i know you can specified the number of processors do you want to run dedicated to one process. 2008/10/10 Scott Marlowe <[EMAIL PROTECTED]>: > On Thu, Oct 9, 2008 at 3:34 PM, Bart Grantham <[EMAIL PROTECTED]> wrote: >> Forgive me if this has been beaten into the ground, but my team and I >> couldn't find much conclusive study or posts on this issue. To make a long >> story short: we're experiencing Xeons as 50% slower than Opterons, even when >> the Xeon has twice as much cache and a slight clock speed advantage. > > I'm not sure what causes this issue either, although I suspect it's > the inter-CPU / CPU to memory communication speeds that make the > difference. It seems that as the number of CPUs increase, the opteron > lead increases over the xeon. > > -- > 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] Question about hstore
Ivan Pavlov <[EMAIL PROTECTED]> writes: > So my question is: what is the difference between hstore and ghstore? AFAICS ghstore is the storage type used in gist indexes for hstore columns. Presumably it's not documented because it's not useful for user purposes. 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] Opteron vs. Xeon performance differences
On Thu, Oct 9, 2008 at 3:34 PM, Bart Grantham <[EMAIL PROTECTED]> wrote: > Forgive me if this has been beaten into the ground, but my team and I > couldn't find much conclusive study or posts on this issue. To make a long > story short: we're experiencing Xeons as 50% slower than Opterons, even when > the Xeon has twice as much cache and a slight clock speed advantage. I'm not sure what causes this issue either, although I suspect it's the inter-CPU / CPU to memory communication speeds that make the difference. It seems that as the number of CPUs increase, the opteron lead increases over the xeon. -- 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] GROUP BY does not follow SQL standard
On Thu, Oct 9, 2008 at 8:01 AM, Tony Marston <[EMAIL PROTECTED]> wrote: > The Postgresql implementation of GROUP BY does not conform to either the > 1999 or 2003 SQL standard. The documentation states that every field in the > SELECT list which is not aggregated must be specified in the GROUP BY > clause. While this was true in the 1992 standard, in 1999 this was changed > to "any non-aggregated column appearing in the SELECT list is functionally > dependent upon the GROUP BY clause". In the example both p.name and p.price > are functionally dependent on product_id, therefore there is no need for > them to be included in the GROUP BY clause. > > In this respect Postgresql is wrong Correct. This has been discussed in the past a few times. No one has stepped up to add the functionality to postgres yet though. > and MySQL is right. I wouldn't push it. MySQL also lets you include columns that are not functionally dependent on other grouped by fields as well, and just gives you whichever row data pops up at the moment when it should throw a function. Postgres doesn't quite give you enough rope to get the job done by the spec, MySQL gives you enough rope to hang yourself and everyone for the tri-county area. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] left join conditon causes error
Query SELECT bilkaib.* FROM prpalk, (SELECT TRUE AS db, 1 AS sign UNION SELECT FALSE,-1 ) role,konto destkonto,bilkaib LEFT JOIN kurss sihrkurs ON CASE WHEN '00' IN(cr,db) THEN bilkaib.kuupaev-1 ELSE bilkaib.kuupaev END =sihrkurs.kuupaev AND sihrkurs.raha=destkonto.raha WHERE CASE WHEN role.db THEN bilkaib.db ELSE bilkaib.cr END=destkonto.kontonr AND (destkonto.raha=prpalk.pohiraha OR sihrkurs.raha=destkonto.raha ) AND bilkaib.summa<>0 AND destkonto.kontonr<>'00' AND (bilkaib.kuupaev='2008-01-01 00:00:00.00' OR (bilkaib.cr!='00' AND bilkaib.db!='00')) AND destkonto.kontonr LIKE '111%' ESCAPE '!' AND bilkaib.kuupaev BETWEEN '2008-01-01 00:00:00.00' AND '2008-01-03 00:00:00.00' causes error ERROR: invalid reference to FROM-clause entry for table "destkonto" LINE 7: ... =sihrkurs.kuupaev AND sihrkurs.raha=destkonto ^ HINT: There is an entry for table "destkonto", but it cannot be referenced from this part of the query. I tried to remove AND sihrkurs.raha=destkonto.raha and move it to where clause: AND (destkonto.raha=prpalk.pohiraha OR sihrkurs.raha=destkonto.raha ) from left join but in this case query returns duplicate rows from bilkaib table. Why destkonto table cannot used but other tables in FROM list can be used in join condition ? How to use such left join ? Andrus. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Using subquery or creating temp table
Test table: CREATE TABLE t1 ( col1 int, col2 int, ... ); Subquery SELECT * FROM t1 WHERE col1=2 Is it OK to use this subquery two times in same statement or should temp table created to prevent subquery executing twice? Which is better SELECT * ( SELECT * FROM (SELECT * FROM t1 WHERE col1=2) p1 WHERE col2=3 UNION ALL SELECT * FROM (SELECT * FROM t1 WHERE col1=2) p2 WHERE col2=4 ) p3 GROUP BY 1; or CREATE TEMP TABLE temp ON COMMIT DROP AS SELECT * FROM t1 WHERE col1=2; SELECT * ( SELECT * FROM temp p1 WHERE col2=3 UNION ALL SELECT * FROM temp p2 WHERE col2=4 ) p3 GROUP BY 1 ? In real query select statements above contain several tables and have more sophisticated where clauses. Using PostgreSQL 8.0+ Andrus. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] GROUP BY does not follow SQL standard
The Postgresql implementation of GROUP BY does not conform to either the 1999 or 2003 SQL standard. The documentation states that every field in the SELECT list which is not aggregated must be specified in the GROUP BY clause. While this was true in the 1992 standard, in 1999 this was changed to "any non-aggregated column appearing in the SELECT list is functionally dependent upon the GROUP BY clause". In the example both p.name and p.price are functionally dependent on product_id, therefore there is no need for them to be included in the GROUP BY clause. In this respect Postgresql is wrong and MySQL is right. -- Tony Marston http://www.tonymarston.net http://www.radicore.org -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] problem with check constraints
Hi, When I create a check constraint in PgAdmin3 1.8.4 on a Postgresql 8.3.3: ((A and B) or (C and D)) I get with create script: (A and B or C and D) which is wrong. Please help. Cheers, Anton -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Opteron vs. Xeon performance differences
Forgive me if this has been beaten into the ground, but my team and I couldn't find much conclusive study or posts on this issue. To make a long story short: we're experiencing Xeons as 50% slower than Opterons, even when the Xeon has twice as much cache and a slight clock speed advantage. The full story: we have an older production server with 2G of RAM, 2.4GHz Opterons w/ 1M of cache. The database is not large, only around 7M or 8M rows altogether, 2.5G on disk. Most queries are reads, probably on a 10:1 proportion with writes. In the process of upgrading this server to a pair of DRBD-mirrored (more on this below) servers we discovered that the new servers were actually slower than the older one. The newer servers have 4G of RAM, 3.0GHz Xeons with 2M of cache. And not just a little slower, but queries (simple, complex, and disgusting recursive stored procedures) routinely run in 50-100% more time than they did on the older server. After many troubleshooting techniques (downgrading the kernel to that of the older machine, verifying version parity, copying the binary from the older server, building a 32bit binary on the new servers, running the entire database out of a ramdisk, and of course much tweaking of postgresql.conf) and seeing virtually no benefit from any of these tests I finally took the final leap: just pull the disks and throw them in a newer Opteron chassis (2.8GHz, 1M cache). And whaddya know? It's got a 20% speed edge on the older Opteron, and blows away the performance of the newer Xeons. One of my guys did some testing and it appears that LWLockAquire and LWLockRelease are the culprits, but we're not entirely confident of our conclusion. Any thoughts on why this might be so different between the two architectures? We're a hosting provider so we've got some spare equipment to work with and I'm going to request that we keep these two boxes up for a week or so. Are there any other tests that you guys can suggest that would help get down to the bottom of this? I figure that not everyone has access to as much gear as we do so it might be a good opportunity to get some A/B testing on a production database on identical OS/server installs on different hardware. I'm content to just say "Well, we use Opterons then!", but I imagine that if we could help bring equal performance to Xeon users that it would be worth the effort of volunteering. To be clear, I have two machines sitting on the network ready for tweaking, one is a Xeon, the other is an Opteron, neither is in production and both can be fully mangled in the interest of figuring this out. Speaking of being a hosting provider, I may as well take a moment to point out that we are working with DRBD for mirroring and have found it works beautifully with PG (MySQL as well). Also, while our "Managed Database Service" product is geared around MySQL, Oracle, and MSSQL, we're pretty familiar with PG and would be happy to talk to anyone about hosting needs they may have. Thanks for listening, and again please let me know if there is further testing we can do to help get to the bottom of this Opteron/Xeon performance discrepancy. Bart Grantham VP of R&D Logicworks, Inc. www.logicworks.net
Re: [GENERAL] [Pkg-postgresql-public] Postgres major version support policy on Debian
* Martin Pitt <[EMAIL PROTECTED]> [2008-10-10 09:49:01 CEST]: > Alexander Wirt [2008-10-10 7:02 +0200]: > > mechanized? No. > > I meant it in the sense of "run a script to create a backport from a > particular testing/unstable release, as opposed to changing any source > package and upload it manually to backports.org". I would very much > assume that this is what currently happens with backports.org. At > least that's how we do backports in Ubuntu, with "backport-source.py > package_name source_release". Erm, the source package _has_ to be changed, the version has to get adapted and the likes, for a start, propably even build-dependencies. And it's expected that people uploading their packages to backports apply similar testing to their uploads than they do with uploads to unstable. > > Only if they are tested carefully. > > Goes without saying. mechanized didn't sound like that, to be honest. > > And I still don't like this. > > --verbose ? ,--> quote yourself <-- | So a compromise I can live with is to put it back into unstable (or | even just experimental), but never let it propagate to testing. Then | backports.org can do mechanized backports of updates without being | tied to the long lifecycle of Lenny. Would that be an acceptable | compromise for all involved parties? `--> quote yourself <-- Backports are meant to sit between stable and testing so that people can upgrade to the next stable release without any major headaches. If you backport from "unstable (or even just experimental)" you lose this approach totally and fail with what backports.org is trying to achieve. This is what formorer doesn't like, and honestly, as much as I would like to help getting things working again and support postgres users here, I have to agree with him. So long, Rhonda -- 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] [Pkg-postgresql-public] Postgres major version support policy on Debian
* Markus Wanner <[EMAIL PROTECTED]> [2008-10-10 10:27:51 CEST]: > Gerfried Fuchs wrote: > > This is what formorer doesn't like, and honestly, as much as I would > > like to help getting things working again and support postgres users > > here, I have to agree with him. > > What solution do you have in mind for people who want Postgres 8.2 on > debian etch (because they had it once it has been offered by backports)? Upgrade to pg8.3, the same that users of testing would have to do. And learn to see that backported packages are a moving target that gets updated. pitti has made it clear that he can't reasonably support pg8.2 himself side-a-side for lenny. Your offer was there to help out with that approach but you don't seem to want to go that path neither. Don't blame me for that. > So far I've only read that you don't like what's proposed, but I'm > missing any kind of a proposal for a solution of the problem. So far I've only read that you don't like what's proposed, but I'm missing any kind of a proposal from you for a solution of the problem. Just because you personally don't like a proposal doesn't mean that it's not there, and I don't want to start it all over again. As your company seem to have quite some interest in it, there might be yet another proposal: How about hiring someone to support it properly? Please notice again that all of us are working voluntary on it and that we all usually are pretty tight set with our spare time. And no, I am not proposing to pay me for doing so, I wouldn't accept it because it definitely would look too fishy and might get me compared to some former DPL behavior. So long, Rhonda -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Question about hstore
I installed the hstore module (http://www.postgresql.org/docs/current/ static/hstore.html) because I have a situation where I prefer to use hstore instead of XML. Everything works great, but I saw that after installing the module I actually have two new datatypes: hstore and ghstore. The last one is not documented. So my question is: what is the difference between hstore and ghstore? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Re: [Pkg-postgresql-public] Postgres major version support policy on Debian
Markus Wanner schrieb am Donnerstag, den 09. Oktober 2008: > Hi, > > Martin Pitt wrote: > > That's in fact the option I have most trouble with. Reason is that > > major upstream releases are roughly maintained for five years. All > > packages in Lenny main will be supported for Lenny's lifetime, which > > is in the order of 4 years (time to release plus, say, 3 years until > > the next Debian release comes out, plus one year of "oldstable" > > security/bug fix support). > > Understood. > > > However, postgresql-8.2 is already a little less than 2 years old, > > which means that we will need to backport patches in Debian for over a > > year. I think it will just barely work with supporting 8.1 in Etch and > > 8.3 in Lenny, but 8.2 will mean trouble. That's the primary reason > > why I only want to support the latest version in a stable release. I > > just can't commit to doing all that backporting work myself. > > I didn't mean to put more work on your shoulders. Quite the opposite, in > fact. > > > So a compromise I can live with is to put it back into unstable (or > > even just experimental), but never let it propagate to testing. Then > > backports.org can do mechanized backports of updates without being mechanized? No. Only if they are tested carefully. And I still don't like this. Alex -- Alexander Wirt, [EMAIL PROTECTED] CC99 2DDD D39E 75B0 B0AA B25C D35B BC99 BC7D 020A -- 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] Re: [Pkg-postgresql-public] Postgres major version support policy on Debian
* Scott Marlowe <[EMAIL PROTECTED]> [2008-10-06 18:07:39 CEST]: > On Mon, Oct 6, 2008 at 9:34 AM, Markus Wanner <[EMAIL PROTECTED]> wrote: > > Well, it's a general Postgres problem, not a Debian one. Upgrading > > between major versions requires a full dump/restore cycle, for which the > > downtime is proportional to the database size. For small or medium > > databases that's not an issue, but above some Gigabytes, that begins to > > hurt pretty badly. > > In that case I prefer to have both db versions available and use slony > to upgrade in place. We recently upgraded from 8.1 to 8.3 and work > the downtime was measured in seconds (the time it took slony to switch > the two servers). Good to hear. Though I see another problem here, slony is always only available for a single postgres version in the current packaging, so that upgrading path isn't that easy as you make it sound... At least not if you want to do it on a single system and not through two different machines. So long. :) Rhonda -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Socket error instead of Access denied
One thing I forgot. The errors occur when I use psql.exe with the -U paramter -- Forwarded message -- From: Serge Fonville <[EMAIL PROTECTED]> Date: Fri, Oct 10, 2008 at 3:00 PM Subject: Fwd: Socket error instead of Access denied To: pgsql-general Sorry about that, submitted it to early. Here goes again: Recently I started to move from MySQL to PostgreSQL.When I had it set up on windows, I enabled ssl and everything worked like a charm. My pg_hba.conf looks like this: hostssl all postgres 127.0.0.1/32 md5 hostssl ticketsystem ticketsystem127.0.0.1/32 md5 hostssl faq faq 127.0.0.1/32 md5 hostssl all postgres ::1/128 md5 hostssl ticketsystem ticketsystem::1/128 md5 hostssl faq faq ::1/128 md5 If I access the database to which the user has access, no problem, If I use the user faq to connecto to ticketsystem I'd exect an access denied of some sort, instead I get: could not receive data from server: Unknown socket error (0x0002/2) Previous connection kept Is this due to the SSL connection, or is it related to some other error And is it 'fixable' I already searched the mailing list and google, but to no avail. Thanks in advance Serge Fonville
[GENERAL] Fwd: Socket error instead of Access denied
Sorry about that, submitted it to early. Here goes again: Recently I started to move from MySQL to PostgreSQL.When I had it set up on windows, I enabled ssl and everything worked like a charm. My pg_hba.conf looks like this: hostssl all postgres 127.0.0.1/32 md5 hostssl ticketsystem ticketsystem127.0.0.1/32 md5 hostssl faq faq 127.0.0.1/32 md5 hostssl all postgres ::1/128 md5 hostssl ticketsystem ticketsystem::1/128 md5 hostssl faq faq ::1/128 md5 If I access the database to which the user has access, no problem, If I use the user faq to connecto to ticketsystem I'd exect an access denied of some sort, instead I get: could not receive data from server: Unknown socket error (0x0002/2) Previous connection kept Is this due to the SSL connection, or is it related to some other error And is it 'fixable' I already searched the mailing list and google, but to no avail. Thanks in advance Serge Fonville
Re: [GENERAL] flood in logs
hubert depesz lubaczewski <[EMAIL PROTECTED]> writes: > On Fri, Oct 10, 2008 at 08:42:02AM -0400, Tom Lane wrote: >> Mph, so it's an omission in the log_statement code. You must have >> log_statement set to mod or ddl on this machine. > yes. it's ddl. and on the machine that doesn't print the warning - it's > "none". Right, we don't bother to call GetCommandLogLevel if the setting is ALL or NONE, so the oversight isn't exposed by default. > so, for now i think it's better to set it to 'none' - and wait for > new release with fix? Either that or patch it locally. I wonder if there are any other omissions in that function... 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] Socket error instead of Access denied
Recently I started to move from MySQL to PostgreSQL.When I had it set up on windows, I enabled ssl and everything worked like a charm. My pg_hba.conf looks like this: hostsslallpostgres 127.0.0.1/32 md5 hostssl ticketsystem ticketsystem127.0.0.1/32md5 hostsslfaq faq 127.0.0.1/32 md5 hostssl allpostgres::1/128 md5 hostssl ticketsystemticketsystem::1/128md5 hostssl faq faq ::1/128 md5
Re: [GENERAL] flood in logs
On Fri, Oct 10, 2008 at 08:42:02AM -0400, Tom Lane wrote: > Mph, so it's an omission in the log_statement code. You must have > log_statement set to mod or ddl on this machine. yes. it's ddl. and on the machine that doesn't print the warning - it's "none". I also checked current (well, from 2 days ago) head - and the problem is there as well. so, for now i think it's better to set it to 'none' - and wait for new release with fix? best regards, depesz -- Linkedin: http://www.linkedin.com/in/depesz / blog: http://www.depesz.com/ jid/gtalk: [EMAIL PROTECTED] / aim:depeszhdl / skype:depesz_hdl / gg:6749007 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Idle in transaction connection
Hi all I have a Postgresql 8.2.10 install running on w2k3, and recently, or more precisely, after I upgraded from 8.2.5 to 8.2.10, some connections keep in the 'idle in transaction' state. The real problem is that even after I kill the connection subprocess, the pg_stat_activity reports that the connection is still there, and the locks too. The only thing that solves this is restarting Postgres. Any clues of what might be happening? Thanks for tha attention. -- 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] flood in logs
hubert depesz lubaczewski <[EMAIL PROTECTED]> writes: > postgres=# DISCARD ALL; > WARNING: 01000: unrecognized node type: 742 > LOCATION: GetCommandLogLevel, utility.c:2558 > DISCARD ALL Mph, so it's an omission in the log_statement code. You must have log_statement set to mod or ddl on this machine. 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] flood in logs
On Fri, Oct 10, 2008 at 08:24:35AM -0400, Tom Lane wrote: > hubert depesz lubaczewski <[EMAIL PROTECTED]> writes: > > postgres=# DISCARD ALL; > > WARNING: unrecognized node type: 742 > > DISCARD ALL > Could we see that with "\set VERBOSITY verbose", please? sure: postgres=# DISCARD ALL; WARNING: 01000: unrecognized node type: 742 LOCATION: GetCommandLogLevel, utility.c:2558 DISCARD ALL Best regards, depesz -- Linkedin: http://www.linkedin.com/in/depesz / blog: http://www.depesz.com/ jid/gtalk: [EMAIL PROTECTED] / aim:depeszhdl / skype:depesz_hdl / gg:6749007 -- 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] flood in logs
hubert depesz lubaczewski <[EMAIL PROTECTED]> writes: > postgres=# DISCARD ALL; > WARNING: unrecognized node type: 742 > DISCARD ALL Could we see that with "\set VERBOSITY verbose", please? 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] Update Query Problem
am Fri, dem 10.10.2008, um 12:38:24 +0100 mailte Jeng Yu folgendes: > Hi People! > > I'm doing an application and I've chosen postgresql > for the backend db. I need to use SQL update command > like this in my application: > > update mytable set x='20' where id='someid' order by > id limit 1; Du you have a primary key? If you don't have a pk, you can use the ctid: test=# create table a ( id int, val int); CREATE TABLE test=# insert into a values (1,1); INSERT 0 1 test=# insert into a values (1,1); INSERT 0 1 test=# insert into a values (1,1); INSERT 0 1 test=# select * from a; id | val +- 1 | 1 1 | 1 1 | 1 (3 rows) test=# begin; BEGIN test=# update a set val=2 where ctid=(select ctid from a where id=1 limit 1); UPDATE 1 test=# select * from a; id | val +- 1 | 1 1 | 1 1 | 2 (3 rows) Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net -- 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] flood in logs
On Fri, Oct 10, 2008 at 12:58:35PM +0200, Sebastian Pawłowski wrote: > have you got any idea where do they come from? and how can i stop them? i > was trying to solve the problem, but with no result > i'm using: > Ubuntu 8.04.1 > PostgreSQL 8.3.3 on x86_64-pc-linux-gnu, compiled by GCC cc (GCC) 4.2.3 > (Ubuntu 4.2.3-2ubuntu7) > pgbouncer version 1.2.3 additional information: 1. postgresql from ubuntu packages: $ dpkg -l | grep -E '(libpq|postgres)' ii libpq-dev 8.3.3-0ubuntu0.8.04 header files for libpq5 (PostgreSQL library) ii libpq58.3.3-0ubuntu0.8.04 PostgreSQL C client library ii postgresql8.3.3-0ubuntu0.8.04 object-relational SQL database (latest versi ii postgresql-8.38.3.3-0ubuntu0.8.04 object-relational SQL database, version 8.3 ii postgresql-client 8.3.3-0ubuntu0.8.04 front-end programs for PostgreSQL (latest ve ii postgresql-client-8.3 8.3.3-0ubuntu0.8.04 front-end programs for PostgreSQL 8.3 ii postgresql-client-common 87 manager for multiple PostgreSQL client versi ii postgresql-common 87 PostgreSQL database-cluster manager ii postgresql-contrib8.3.3-0ubuntu0.8.04 additional facilities for PostgreSQL (latest ii postgresql-contrib-8.38.3.3-0ubuntu0.8.04 additional facilities for PostgreSQL ii postgresql-doc8.3.3-0ubuntu0.8.04 documentation for the PostgreSQL database ma ii postgresql-doc-8.38.3.3-0ubuntu0.8.04 documentation for the PostgreSQL database ma ii postgresql-server-dev-8.3 8.3.3-0ubuntu0.8.04 development files for PostgreSQL 8.3 server- and, i was able to determine that the problem occurs when i execute 'discard all' query - it is usually being run in our case by pgbouncer: postgres=# DISCARD ALL; WARNING: unrecognized node type: 742 DISCARD ALL on another machine with the same packages - the problem doesn't exist. what could be the reason behind it? best regards, depesz -- Linkedin: http://www.linkedin.com/in/depesz / blog: http://www.depesz.com/ jid/gtalk: [EMAIL PROTECTED] / aim:depeszhdl / skype:depesz_hdl / gg:6749007 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Update Query Problem
Hi People! I'm doing an application and I've chosen postgresql for the backend db. I need to use SQL update command like this in my application: update mytable set x='20' where id='someid' order by id limit 1; In other words, I want to update just one row of the table if there are more than one row that the query finds. It doesn't work for me in postgresql. What's the way I can do this in postgresql? Please forgive me if this has been answered before. Please point me to the right doc. I couldn't readily find the answer. Thanks, Jeng Yu -- 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] Frustrated...pg_dump/restore
Jeff Amiel wrote: > Ahhh > *looks at encoding* > > Well..they are both the same...BUT...they are set to > ENCODING = 'SQL_ASCII'; > > That explains a lotthey should probably be set to Unicode UTF8 > Duh > > Any way to change encoding without dumping/restoring database? You can change client encoding any time with the PGCLIENTENCODING environment variable. AFAIK, there's no way to change the encoding of a database, it's set at creation time. But I think SQL_ASCII makes it less picky about the input, so that might not be the source of your problem. You should look at the errors you see _before_ the "invalid command \N". I suspect a slight schema mismatch... that could cause a COPY to fail, while an INSERT might still work. How did you create the 'schema-only database'? With a pg_dump --schema-only or with a different SQL script? You may also try and pg_dump --schema-only both databases and diff the output. .TM. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] flood in logs
hi, i''m getting a lot of warning lines in log file, they appears as follows: 2008-10-10 12:11:49.976 CEST [EMAIL PROTECTED] 5696 127.0.0.1(46403) WARNING: unrecognized node type: 742 2008-10-10 12:11:49.987 CEST [EMAIL PROTECTED] 5696 127.0.0.1(46403) WARNING: unrecognized node type: 742 2008-10-10 12:11:50.004 CEST [EMAIL PROTECTED] 5696 127.0.0.1(46403) WARNING: unrecognized node type: 742 2008-10-10 12:11:50.015 CEST [EMAIL PROTECTED] 5696 127.0.0.1(46403) WARNING: unrecognized node type: 742 2008-10-10 12:11:50.460 CEST [EMAIL PROTECTED] 5696 127.0.0.1(46403) WARNING: unrecognized node type: 742 2008-10-10 12:11:50.471 CEST [EMAIL PROTECTED] 5696 127.0.0.1(46403) WARNING: unrecognized node type: 742 2008-10-10 12:11:50.490 CEST [EMAIL PROTECTED] 5696 127.0.0.1(46403) WARNING: unrecognized node type: 742 2008-10-10 12:11:50.504 CEST [EMAIL PROTECTED] 5696 127.0.0.1(46403) WARNING: unrecognized node type: 742 from 2M lines only 200K was different then these have you got any idea where do they come from? and how can i stop them? i was trying to solve the problem, but with no result i'm using: Ubuntu 8.04.1 PostgreSQL 8.3.3 on x86_64-pc-linux-gnu, compiled by GCC cc (GCC) 4.2.3 (Ubuntu 4.2.3-2ubuntu7) pgbouncer version 1.2.3 Thanks in advance, Reagards, Sebastian Pawlowski -- 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 get unique identifier for a client
I would gather all possible inputs of data that you can use for it. Will the data be stored in one place for all customers ? if so - you'll be okay with uuid of some sort, as primary key on a table. if you need data to depend on outside data, either write a query, that would generate it, taking those outside params as input, or write C procedure, that will gather that data, and return some unique key. So it all depends on what are you actually trying to achieve.
[GENERAL] how to get unique identifier for a client
hi all is there a way to get a unique identifier for a client? something like a machine id. session_user does not seem to work since a user can log on to many workstations. been through the list so i'm not searching for getting the ip address since the list said something like it's not supported in unix-sockets, though i'll take it since i'm using postgres in windows environment. thanks in advance :) -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Re: [Pkg-postgresql-public] Postgres major version support policy on Debian
Hi, Gerfried Fuchs wrote: > Upgrade to pg8.3, the same that users of testing would have to do. And > learn to see that backported packages are a moving target that gets > updated. The problem only exists because upgrading is not an option. There are lots of people *wanting* to stick with Postgres 8.2 for good reasons. As long as you don't see and accept that as a problem, we keep talking across each other. > pitti has made it clear that he can't reasonably support pg8.2 himself > side-a-side for lenny. Your offer was there to help out with that > approach but you don't seem to want to go that path neither. Don't blame > me for that. My offer is to support Postgres 8.2 even for etch, as a kind of a "backport" (not in the sense of a Debian-backports.org-backport, but a backport of "newer" software to Debian etch). If that easily gives us Postgres 8.2 for lenny as well, even better! I probably want Postgres 8.2 also for lenny, as soon as that becomes stable. To help others in the same situation, I would like to offer these packages via some half-ways official channel. That turned out to be harder than I thought. >> So far I've only read that you don't like what's proposed, but I'm >> missing any kind of a proposal for a solution of the problem. > > So far I've only read that you don't like what's proposed, but I'm > missing any kind of a proposal from you for a solution of the problem. Obviously we are talking about different problems. I'm talking about making Postgres 8.2 available for etch, because that's what I need. I am providing a solution to that problem in form of a custom repository on www.bluegap.ch/debian. Regards Markus Wanner -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Re: [Pkg-postgresql-public] Postgres major version support policy on Debian
Hi, Gerfried Fuchs wrote: > This is what formorer doesn't like, and honestly, as much as I would > like to help getting things working again and support postgres users > here, I have to agree with him. What solution do you have in mind for people who want Postgres 8.2 on debian etch (because they had it once it has been offered by backports)? So far I've only read that you don't like what's proposed, but I'm missing any kind of a proposal for a solution of the problem. Regards Markus Wanner -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Re: [Pkg-postgresql-public] Postgres major version support policy on Debian
Alexander Wirt [2008-10-10 7:02 +0200]: > > > So a compromise I can live with is to put it back into unstable (or > > > even just experimental), but never let it propagate to testing. Then > > > backports.org can do mechanized backports of updates without being > mechanized? No. I meant it in the sense of "run a script to create a backport from a particular testing/unstable release, as opposed to changing any source package and upload it manually to backports.org". I would very much assume that this is what currently happens with backports.org. At least that's how we do backports in Ubuntu, with "backport-source.py package_name source_release". I didn't mean "automatically move every -8.2 unstable upload to -backports", of course. > Only if they are tested carefully. Goes without saying. > And I still don't like this. --verbose ? Thanks, Martin -- Martin Pitt| http://www.piware.de Ubuntu Developer (www.ubuntu.com) | Debian Developer (www.debian.org) -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Re: [Pkg-postgresql-public] Postgres major version support policy on Debian
Markus Wanner [2008-10-09 22:53 +0200]: > Can you act as a sponsor for uploading 8.2 packages to experimental or > unstable? Of course. Martin -- Martin Pitt| http://www.piware.de Ubuntu Developer (www.ubuntu.com) | Debian Developer (www.debian.org) -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
