pg.ddx.io pgsql-general@postgresql.org mailing list archive
help / color / mirror / Atom feedbug or feature?
7+ messages / 7 participants
[nested] [flat]
* bug or feature?
@ 1999-11-25 16:54 Reto H. <no_spam_4me_cut_here_retoh@dplanet.ch>
0 siblings, 0 replies; 7+ messages in thread
From: Reto H. @ 1999-11-25 16:54 UTC (permalink / raw)
To: pgsql-general@postgresql.org.pgsql-patches
Data type 'ABSTIME' seems to have a kind of Y2K bug
Highest date you can store is Dec 31, 2011
Is there a patch out there?
Thanks for any suggestions.
<Reto> retoh@dplanet.ch
^ permalink raw reply [nested|flat] 7+ messages in thread
* Bug or feature?
@ 2002-05-20 11:15 Kostyuk Oleg <cub@digma.kharkov.ua>
0 siblings, 1 reply; 7+ messages in thread
From: Kostyuk Oleg @ 2002-05-20 11:15 UTC (permalink / raw)
To: pgsql-general
pgSQL 7.2.1, table with ~590000 rows with next stuct:
user_id integer
url text
.................... -- other fields
i do VACUUM ANALYZE, and then have this results:
1) ----------
proxy=> EXPLAIN SELECT count(user_id) AS cnt, user_id FROM stats GROUP
BY user_id ORDER BY cnt DESC;
NOTICE: QUERY PLAN:
Sort (cost=94399.52..94399.52 rows=58842 width=4)
-> Aggregate (cost=86383.49..89325.61 rows=58842 width=4)
-> Group (cost=86383.49..87854.55 rows=588425 width=4)
-> Sort (cost=86383.49..86383.49 rows=588425 width=4)
-> Seq Scan on stats (cost=0.00..13873.25
rows=588425 width=4)
2) ----------
proxy=> EXPLAIN SELECT count(url) AS cnt, user_id FROM stats GROUP BY
user_id ORDER BY cnt DESC;
NOTICE: QUERY PLAN:
Sort (cost=155022.05..155022.05 rows=58842 width=51)
-> Aggregate (cost=146163.49..149105.61 rows=58842 width=51)
-> Group (cost=146163.49..147634.55 rows=588425 width=51)
-> Sort (cost=146163.49..146163.49 rows=588425 width=51)
-> Seq Scan on stats (cost=0.00..13873.25
rows=588425 width=51)
-----------end-----------
cost grow by 1.5 times!
yes, i understand - this because url column have big width...
but in count(url) width of column not important! - but used?...
or it's bug of optimizer? or it's not implemented in optimizer?
Thx.
ps : sorry for my bad english :)
--
With best wishes, DIGMA sysadmin
Oleg Kostyuk aka Cub (CUB7-RIPE)
[BSD registered user #BSD050664]
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Bug or feature?
@ 2002-05-21 03:13 Stephan Szabo <sszabo@megazone23.bigpanda.com>
parent: Kostyuk Oleg <cub@digma.kharkov.ua>
0 siblings, 0 replies; 7+ messages in thread
From: Stephan Szabo @ 2002-05-21 03:13 UTC (permalink / raw)
To: Kostyuk Oleg <cub@digma.kharkov.ua>; +Cc: pgsql-general
On Mon, 20 May 2002, Kostyuk Oleg wrote:
> 1) ----------
> proxy=> EXPLAIN SELECT count(user_id) AS cnt, user_id FROM stats GROUP
> BY user_id ORDER BY cnt DESC;
> NOTICE: QUERY PLAN:
>
> Sort (cost=94399.52..94399.52 rows=58842 width=4)
> -> Aggregate (cost=86383.49..89325.61 rows=58842 width=4)
> -> Group (cost=86383.49..87854.55 rows=588425 width=4)
> -> Sort (cost=86383.49..86383.49 rows=588425 width=4)
> -> Seq Scan on stats (cost=0.00..13873.25
> rows=588425 width=4)
>
>
> 2) ----------
> proxy=> EXPLAIN SELECT count(url) AS cnt, user_id FROM stats GROUP BY
> user_id ORDER BY cnt DESC;
> NOTICE: QUERY PLAN:
>
> Sort (cost=155022.05..155022.05 rows=58842 width=51)
> -> Aggregate (cost=146163.49..149105.61 rows=58842 width=51)
> -> Group (cost=146163.49..147634.55 rows=588425 width=51)
> -> Sort (cost=146163.49..146163.49 rows=588425 width=51)
> -> Seq Scan on stats (cost=0.00..13873.25
> rows=588425 width=51)
>
> -----------end-----------
>
> cost grow by 1.5 times!
In general, I'm not sure how meaningful a comparison between explain
outputs from different queries are.
> yes, i understand - this because url column have big width...
> but in count(url) width of column not important! - but used?...
Except that it's probably brought along for the sort step. You
do at least need to know if it's NULL or not, and there's nothing
I can't think of something that would necessarily stop you from redefining
a count aggregate that did something with that value (apart from the
fact it's probably silly). There are optimizations with certain
aggregates that aren't taken because the aggregate system is pretty
general.
^ permalink raw reply [nested|flat] 7+ messages in thread
* bug or feature
@ 2026-10-05 08:26 Carl.Weierstrass <carl.weierstrass@googlemail.com>
0 siblings, 2 replies; 7+ messages in thread
From: Carl.Weierstrass @ 2026-10-05 08:26 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
Hi,
I want to delete some data from table test1 und protocol the deleted
data in table test2:
The solution with two statements
begin;
insert into test2
select * from test1
where 1 = 1;
delete from test1
where 1 = 1
returning *; -- show deleted lines
commit;
works fine, but the more elegant version in one single statement results
in a syntax error
begin;
insert into test2
delete from test1
where 1 = 1
returning *; --return deleted lines
commit;
psql:test.sql:32: FEHLER: Syntaxfehler bei »delete«
ZEILE 2: delete from test1
Is this a bug or a feature?
Charly
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: bug or feature
@ 2026-10-05 14:12 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Carl.Weierstrass <carl.weierstrass@googlemail.com>
1 sibling, 0 replies; 7+ messages in thread
From: Laurenz Albe @ 2026-10-05 14:12 UTC (permalink / raw)
To: Carl.Weierstrass <carl.weierstrass@googlemail.com>; pgsql-general@lists.postgresql.org
On Mon, 2026-10-05 at 10:26 +0200, Carl.Weierstrass wrote:
> I want to delete some data from table test1 und protocol the deleted
> data in table test2:
>
> The solution with two statements
>
> begin;
> insert into test2
> select * from test1
> where 1 = 1;
>
> delete from test1
> where 1 = 1
> returning *; -- show deleted lines
> commit;
>
> works fine, but the more elegant version in one single statement results
> in a syntax error
>
> begin;
> insert into test2
> delete from test1
> where 1 = 1
> returning *; --return deleted lines
> commit;
>
> psql:test.sql:32: FEHLER: Syntaxfehler bei »delete«
> ZEILE 2: delete from test1
>
> Is this a bug or a feature?
That's incorrect syntax.
Try
WITH cte AS (
DELETE FROM test1
RETURNING *
)
INSERT INTO test2
SELECT * FROM cte;
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: bug or feature
@ 2026-10-05 14:32 David G. Johnston <david.g.johnston@gmail.com>
parent: Carl.Weierstrass <carl.weierstrass@googlemail.com>
1 sibling, 1 reply; 7+ messages in thread
From: David G. Johnston @ 2026-10-05 14:32 UTC (permalink / raw)
To: Carl.Weierstrass <carl.weierstrass@googlemail.com>; +Cc: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
On Monday, October 5, 2026, Carl.Weierstrass <
carl.weierstrass@googlemail.com> wrote:
> but the more elegant version in one single statement results in a syntax
> error
>
> begin;
> insert into test2
> delete from test1
> where 1 = 1
> returning *; --return deleted lines
> commit;
>
> psql:test.sql:32: FEHLER: Syntaxfehler bei »delete«
> ZEILE 2: delete from test1
>
> Is this a bug or a feature?
If you make something up that isn’t documented it shouldn’t work. You can
call the inability to interpret random stuff you send to the server a
feature if you need an apply a label.
You might want to look at the WITH clause of those commands to see how to
go about combining multiple distinct commands into one execution correctly.
David J.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: bug or feature
@ 2026-10-05 14:53 Adrian Klaver <adrian.klaver@aklaver.com>
parent: David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 0 replies; 7+ messages in thread
From: Adrian Klaver @ 2026-10-05 14:53 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; Carl.Weierstrass <carl.weierstrass@googlemail.com>; +Cc: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
On 10/5/26 7:32 AM, David G. Johnston wrote:
> On Monday, October 5, 2026, Carl.Weierstrass
> <carl.weierstrass@googlemail.com
> <mailto:carl.weierstrass@googlemail.com>> wrote:
>
> but the more elegant version in one single statement results in a
> syntax error
>
> begin;
> insert into test2
> delete from test1
> where 1 = 1
> returning *; --return deleted lines
> commit;
>
> psql:test.sql:32: FEHLER: Syntaxfehler bei »delete«
> ZEILE 2: delete from test1
>
> Is this a bug or a feature?
>
>
> If you make something up that isn’t documented it shouldn’t work. You
> can call the inability to interpret random stuff you send to the server
> a feature if you need an apply a label.
>
> You might want to look at the WITH clause of those commands to see how
> to go about combining multiple distinct commands into one execution
> correctly.
In particular:
https://www.postgresql.org/docs/current/queries-with.html#QUERIES-WITH-MODIFYING
>
> David J.
>
--
Adrian Klaver
adrian.klaver@aklaver.com
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2026-10-05 14:53 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-11-25 16:54 bug or feature? Reto H. <no_spam_4me_cut_here_retoh@dplanet.ch>
2002-05-20 11:15 Bug or feature? Kostyuk Oleg <cub@digma.kharkov.ua>
2002-05-21 03:13 ` Re: Bug or feature? Stephan Szabo <sszabo@megazone23.bigpanda.com>
2026-10-05 08:26 bug or feature Carl.Weierstrass <carl.weierstrass@googlemail.com>
2026-10-05 14:12 ` Re: bug or feature Laurenz Albe <laurenz.albe@cybertec.at>
2026-10-05 14:32 ` Re: bug or feature David G. Johnston <david.g.johnston@gmail.com>
2026-10-05 14:53 ` Re: bug or feature Adrian Klaver <adrian.klaver@aklaver.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox