public inbox for [email protected]help / color / mirror / Atom feed
[PATCH v37 07/11] pg_ls_*dir to return all the metadata from pg_stat_file.. 5+ messages / 4 participants [nested] [flat]
* [PATCH v37 07/11] pg_ls_*dir to return all the metadata from pg_stat_file.. @ 2020-03-10 02:56 Justin Pryzby <[email protected]> 0 siblings, 0 replies; 5+ messages in thread From: Justin Pryzby @ 2020-03-10 02:56 UTC (permalink / raw) ..but it doesn't seem worth factoring out the common bits, since stat_file doesn't return a name, so all the field numbers are off by one. NOTE, the atime is now shown where the mtime used to be. Need catversion bump --- doc/src/sgml/func.sgml | 34 ++++++--- src/backend/utils/adt/genfile.c | 72 +++++++++----------- src/include/catalog/pg_proc.dat | 42 ++++++------ src/test/regress/expected/misc_functions.out | 40 +++++------ src/test/regress/expected/tablespace.out | 8 +-- 5 files changed, 102 insertions(+), 94 deletions(-) diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index 3a4379058dd..59bbcb796a6 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -25911,7 +25911,10 @@ LOG: Grand total: 1651920 bytes in 201 blocks; 622360 free (88 chunks); 1029560 <returnvalue>setof record</returnvalue> ( <parameter>filename</parameter> <type>text</type>, <parameter>size</parameter> <type>bigint</type>, + <parameter>access</parameter> <type>timestamp with time zone</type>, <parameter>modification</parameter> <type>timestamp with time zone</type>, + <parameter>change</parameter> <type>timestamp with time zone</type>, + <parameter>creation</parameter> <type>timestamp with time zone</type>, <parameter>isdir</parameter> <type>boolean</type> ) </para> <para> @@ -27486,13 +27489,16 @@ SELECT pg_size_pretty(sum(pg_relation_size(relid))) AS total_size <returnvalue>setof record</returnvalue> ( <parameter>name</parameter> <type>text</type>, <parameter>size</parameter> <type>bigint</type>, + <parameter>access</parameter> <type>timestamp with time zone</type>, <parameter>modification</parameter> <type>timestamp with time zone</type>, + <parameter>change</parameter> <type>timestamp with time zone</type>, + <parameter>creation</parameter> <type>timestamp with time zone</type>, <parameter>isdir</parameter> <type>boolean</type> ) </para> <para> For each file in the server's log directory, - return the file's name, size, last modification time (mtime), and a boolean - indicating if the file is a directory. + return the file's name, along with the metadata columns returned by + <function>pg_stat_file</function>. Filenames beginning with a dot and special file types are excluded. </para> <para> @@ -27511,13 +27517,16 @@ SELECT pg_size_pretty(sum(pg_relation_size(relid))) AS total_size <returnvalue>setof record</returnvalue> ( <parameter>name</parameter> <type>text</type>, <parameter>size</parameter> <type>bigint</type>, + <parameter>access</parameter> <type>timestamp with time zone</type>, <parameter>modification</parameter> <type>timestamp with time zone</type>, + <parameter>change</parameter> <type>timestamp with time zone</type>, + <parameter>creation</parameter> <type>timestamp with time zone</type>, <parameter>isdir</parameter> <type>boolean</type> ) </para> <para> For each file in the server's write-ahead log (WAL) directory, list the - file's name, size, last modification time (mtime), and a boolean - indicating if the file is a directory. + file's name along with the metadata columns returned by + <function>pg_stat_file</function>. Filenames beginning with a dot and special files types are excluded. </para> <para> @@ -27609,14 +27618,17 @@ SELECT pg_size_pretty(sum(pg_relation_size(relid))) AS total_size <returnvalue>setof record</returnvalue> ( <parameter>name</parameter> <type>text</type>, <parameter>size</parameter> <type>bigint</type>, + <parameter>access</parameter> <type>timestamp with time zone</type>, <parameter>modification</parameter> <type>timestamp with time zone</type>, + <parameter>change</parameter> <type>timestamp with time zone</type>, + <parameter>creation</parameter> <type>timestamp with time zone</type>, <parameter>isdir</parameter> <type>boolean</type> ) </para> <para> For each file in the server's WAL archive status directory - (<filename>pg_wal/archive_status</filename>), list the file's - name, size, last modification time (mtime), and a boolean indicating if - the file is a directory. + (<filename>pg_wal/archive_status</filename>), list the file's name + along with the metadata columns returned by + <function>pg_stat_file</function>. Filenames beginning with a dot and special file types are excluded. </para> <para> @@ -27636,13 +27648,17 @@ SELECT pg_size_pretty(sum(pg_relation_size(relid))) AS total_size <returnvalue>setof record</returnvalue> ( <parameter>name</parameter> <type>text</type>, <parameter>size</parameter> <type>bigint</type>, + <parameter>access</parameter> <type>timestamp with time zone</type>, <parameter>modification</parameter> <type>timestamp with time zone</type>, + <parameter>change</parameter> <type>timestamp with time zone</type>, + <parameter>creation</parameter> <type>timestamp with time zone</type>, <parameter>isdir</parameter> <type>boolean</type> ) </para> <para> For each file in the temporary directory within the given - <parameter>tablespace</parameter>, return the file's name, size, last - modification time (mtime) and a boolean indicating if the file is a directory. + <parameter>tablespace</parameter>, list the file's name + along with the metadata columns returned by + <function>pg_stat_file</function>. Directories are used for temporary files shared by parallel processes. If <parameter>tablespace</parameter> is not provided, the <literal>pg_default</literal> tablespace is examined. diff --git a/src/backend/utils/adt/genfile.c b/src/backend/utils/adt/genfile.c index 5402ecb3d05..ce4a37a3301 100644 --- a/src/backend/utils/adt/genfile.c +++ b/src/backend/utils/adt/genfile.c @@ -37,6 +37,8 @@ #include "utils/syscache.h" #include "utils/timestamp.h" +static void values_from_stat(struct stat *fst, const char *path, Datum *values, + bool *nulls); static Datum pg_ls_dir_files(FunctionCallInfo fcinfo, const char *dir, int flags); #define LS_DIR_ISDIR (1<<0) /* Show column: isdir */ @@ -464,6 +466,28 @@ pg_read_binary_file_all_missing(PG_FUNCTION_ARGS) PG_RETURN_BYTEA_P(ret); } +/* + * Populate values and nulls from fst and path. + * Used for pg_stat_file() and pg_ls_dir_files() + * nulls is assumed to have been zerod. + */ +static void +values_from_stat(struct stat *fst, const char *path, Datum *values, bool *nulls) +{ + values[0] = Int64GetDatum((int64) fst->st_size); + values[1] = TimestampTzGetDatum(time_t_to_timestamptz(fst->st_atime)); + values[2] = TimestampTzGetDatum(time_t_to_timestamptz(fst->st_mtime)); + /* Unix has file status change time, while Win32 has creation time */ +#if !defined(WIN32) && !defined(__CYGWIN__) + values[3] = TimestampTzGetDatum(time_t_to_timestamptz(fst->st_ctime)); + nulls[4] = true; +#else + nulls[3] = true; + values[4] = TimestampTzGetDatum(time_t_to_timestamptz(fst->st_ctime)); +#endif + values[5] = BoolGetDatum(S_ISDIR(fst->st_mode)); +} + /* * stat a file */ @@ -474,7 +498,7 @@ pg_stat_file(PG_FUNCTION_ARGS) char *filename; struct stat fst; Datum values[6]; - bool isnull[6]; + bool nulls[6]; HeapTuple tuple; TupleDesc tupdesc; bool missing_ok = false; @@ -514,27 +538,9 @@ pg_stat_file(PG_FUNCTION_ARGS) "isdir", BOOLOID, -1, 0); BlessTupleDesc(tupdesc); - memset(isnull, false, sizeof(isnull)); - - values[0] = Int64GetDatum((int64) fst.st_size); - values[1] = TimestampTzGetDatum(time_t_to_timestamptz(fst.st_atime)); - values[2] = TimestampTzGetDatum(time_t_to_timestamptz(fst.st_mtime)); - /* Unix has file status change time, while Win32 has creation time */ -#if !defined(WIN32) && !defined(__CYGWIN__) - values[3] = TimestampTzGetDatum(time_t_to_timestamptz(fst.st_ctime)); - isnull[4] = true; -#else - isnull[3] = true; - values[4] = TimestampTzGetDatum(time_t_to_timestamptz(fst.st_ctime)); -#endif - values[5] = BoolGetDatum(S_ISDIR(fst.st_mode)); -#ifdef WIN32 - /* Links should have isdir=false */ - if (pgwin32_is_junction(filename)) - values[5] = BoolGetDatum(false); -#endif - - tuple = heap_form_tuple(tupdesc, values, isnull); + memset(nulls, false, sizeof(nulls)); + values_from_stat(&fst, filename, values, nulls); + tuple = heap_form_tuple(tupdesc, values, nulls); pfree(filename); @@ -641,8 +647,8 @@ pg_ls_dir_files(FunctionCallInfo fcinfo, const char *dir, int flags) while ((de = ReadDir(dirdesc, dir)) != NULL) { - Datum values[4]; - bool nulls[4]; + Datum values[7]; + bool nulls[7]; char path[MAXPGPATH * 2]; struct stat attrib; @@ -681,24 +687,10 @@ pg_ls_dir_files(FunctionCallInfo fcinfo, const char *dir, int flags) continue; } + memset(nulls, false, sizeof(nulls)); values[0] = CStringGetTextDatum(de->d_name); if (flags & LS_DIR_METADATA) - { - values[1] = Int64GetDatum((int64) attrib.st_size); - values[2] = TimestampTzGetDatum(time_t_to_timestamptz(attrib.st_mtime)); - if (flags & LS_DIR_ISDIR) - { -#ifdef WIN32 - /* Links should have isdir=false */ - if (pgwin32_is_junction(path)) - values[3] = BoolGetDatum(false); - else -#endif - values[3] = BoolGetDatum(S_ISDIR(attrib.st_mode)); - } - } - - memset(nulls, 0, sizeof(nulls)); + values_from_stat(&attrib, path, 1+values, 1+nulls); tuplestore_putvalues(rsinfo->setResult, rsinfo->setDesc, values, nulls); } diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat index 3addb31e45b..29eec1e9045 100644 --- a/src/include/catalog/pg_proc.dat +++ b/src/include/catalog/pg_proc.dat @@ -11712,63 +11712,63 @@ { oid => '3353', descr => 'list files in the log directory', proname => 'pg_ls_logdir', procost => '10', prorows => '20', proretset => 't', provolatile => 'v', prorettype => 'record', proargtypes => '', - proallargtypes => '{text,int8,timestamptz,bool}', proargmodes => '{o,o,o,o}', - proargnames => '{name,size,modification,isdir}', prosrc => 'pg_ls_logdir' }, + proallargtypes => '{text,int8,timestamptz,timestamptz,timestamptz,timestamptz,bool}', proargmodes => '{o,o,o,o,o,o,o}', + proargnames => '{name,size,access,modification,change,creation,isdir}', prosrc => 'pg_ls_logdir' }, { oid => '3354', descr => 'list of files in the WAL directory', proname => 'pg_ls_waldir', procost => '10', prorows => '20', proretset => 't', provolatile => 'v', prorettype => 'record', proargtypes => '', - proallargtypes => '{text,int8,timestamptz,bool}', proargmodes => '{o,o,o,o}', - proargnames => '{name,size,modification,isdir}', prosrc => 'pg_ls_waldir' }, + proallargtypes => '{text,int8,timestamptz,timestamptz,timestamptz,timestamptz,bool}', proargmodes => '{o,o,o,o,o,o,o}', + proargnames => '{name,size,access,modification,change,creation,isdir}', prosrc => 'pg_ls_waldir' }, { oid => '5031', descr => 'list of files in the archive_status directory', proname => 'pg_ls_archive_statusdir', procost => '10', prorows => '20', proretset => 't', provolatile => 'v', prorettype => 'record', - proargtypes => '', proallargtypes => '{text,int8,timestamptz,bool}', - proargmodes => '{o,o,o,o}', proargnames => '{name,size,modification,isdir}', + proargtypes => '', proallargtypes => '{text,int8,timestamptz,timestamptz,timestamptz,timestamptz,bool}', + proargmodes => '{o,o,o,o,o,o,o}', proargnames => '{name,size,access,modification,change,creation,isdir}', prosrc => 'pg_ls_archive_statusdir' }, { oid => '5029', descr => 'list files in the pgsql_tmp directory', proname => 'pg_ls_tmpdir', procost => '10', prorows => '20', proretset => 't', provolatile => 'v', prorettype => 'record', proargtypes => '', - proallargtypes => '{text,int8,timestamptz,bool}', proargmodes => '{o,o,o,o}', - proargnames => '{name,size,modification,isdir}', prosrc => 'pg_ls_tmpdir_noargs' }, + proallargtypes => '{text,int8,timestamptz,timestamptz,timestamptz,timestamptz,bool}', proargmodes => '{o,o,o,o,o,o,o}', + proargnames => '{name,size,access,modification,change,creation,isdir}', prosrc => 'pg_ls_tmpdir_noargs' }, { oid => '5030', descr => 'list files in the pgsql_tmp directory', proname => 'pg_ls_tmpdir', procost => '10', prorows => '20', proretset => 't', provolatile => 'v', prorettype => 'record', proargtypes => 'oid', - proallargtypes => '{oid,text,int8,timestamptz,bool}', proargmodes => '{i,o,o,o,o}', - proargnames => '{tablespace,name,size,modification,isdir}', + proallargtypes => '{oid,text,int8,timestamptz,timestamptz,timestamptz,timestamptz,bool}', proargmodes => '{i,o,o,o,o,o,o,o}', + proargnames => '{tablespace,name,size,access,modification,change,creation,isdir}', prosrc => 'pg_ls_tmpdir_1arg' }, { oid => '6270', descr => 'list of files in the pg_logical/snapshots directory', proname => 'pg_ls_logicalsnapdir', procost => '10', prorows => '20', proretset => 't', provolatile => 'v', prorettype => 'record', - proargtypes => '', proallargtypes => '{text,int8,timestamptz,bool}', - proargmodes => '{o,o,o,o}', proargnames => '{name,size,modification,isdir}', + proargtypes => '', proallargtypes => '{text,int8,timestamptz,timestamptz,timestamptz,timestamptz,bool}', + proargmodes => '{o,o,o,o,o,o,o}', proargnames => '{name,size,access,modification,change,creation,isdir}', prosrc => 'pg_ls_logicalsnapdir' }, { oid => '6271', descr => 'list of files in the pg_logical/mappings directory', proname => 'pg_ls_logicalmapdir', procost => '10', prorows => '20', proretset => 't', provolatile => 'v', prorettype => 'record', - proargtypes => '', proallargtypes => '{text,int8,timestamptz,bool}', - proargmodes => '{o,o,o,o}', proargnames => '{name,size,modification,isdir}', + proargtypes => '', proallargtypes => '{text,int8,timestamptz,timestamptz,timestamptz,timestamptz,bool}', + proargmodes => '{o,o,o,o,o,o,o}', proargnames => '{name,size,access,modification,change,creation,isdir}', prosrc => 'pg_ls_logicalmapdir' }, { oid => '6272', descr => 'list of files in the pg_replslot/slot_name directory', proname => 'pg_ls_replslotdir', procost => '10', prorows => '20', proretset => 't', provolatile => 'v', prorettype => 'record', - proargtypes => 'text', proallargtypes => '{text,text,int8,timestamptz,bool}', - proargmodes => '{i,o,o,o,o}', - proargnames => '{slot_name,name,size,modification,isdir}', + proargtypes => 'text', proallargtypes => '{text,text,int8,timestamptz,timestamptz,timestamptz,timestamptz,bool}', + proargmodes => '{i,o,o,o,o,o,o,o}', + proargnames => '{slot_name,name,size,access,modification,change,creation,isdir}', prosrc => 'pg_ls_replslotdir' }, { oid => '8450', descr => 'list directory with metadata', proname => 'pg_ls_dir_metadata', procost => '10', prorows => '20', proretset => 't', provolatile => 'v', prorettype => 'record', proargtypes => 'text bool bool', - proallargtypes => '{text,bool,bool,text,int8,timestamptz,bool}', proargmodes => '{i,i,i,o,o,o,o}', - proargnames => '{dirname,missing_ok,include_dot_dirs,filename,size,modification,isdir}', + proallargtypes => '{text,bool,bool,text,int8,timestamptz,timestamptz,timestamptz,timestamptz,bool}', proargmodes => '{i,i,i,o,o,o,o,o,o,o}', + proargnames => '{dirname,missing_ok,include_dot_dirs,filename,size,access,modification,change,creation,isdir}', prosrc => 'pg_ls_dir_metadata' }, { oid => '8451', descr => 'list directory with metadata', proname => 'pg_ls_dir_metadata', procost => '10', prorows => '20', proretset => 't', provolatile => 'v', prorettype => 'record', proargtypes => 'text', - proallargtypes => '{text,text,int8,timestamptz,bool}', proargmodes => '{i,o,o,o,o}', - proargnames => '{dirname,filename,size,modification,isdir}', + proallargtypes => '{text,text,int8,timestamptz,timestamptz,timestamptz,timestamptz,bool}', proargmodes => '{i,o,o,o,o,o,o,o}', + proargnames => '{dirname,filename,size,access,modification,change,creation,isdir}', prosrc => 'pg_ls_dir_metadata_1arg' }, # hash partitioning constraint function diff --git a/src/test/regress/expected/misc_functions.out b/src/test/regress/expected/misc_functions.out index 4605fede3f2..fd67508e455 100644 --- a/src/test/regress/expected/misc_functions.out +++ b/src/test/regress/expected/misc_functions.out @@ -349,8 +349,8 @@ select count(*) > 0 as ok from (select pg_ls_waldir()) ss; -- Test not-run-to-completion cases. select * from pg_ls_waldir() limit 0; - name | size | modification | isdir -------+------+--------------+------- + name | size | access | modification | change | creation | isdir +------+------+--------+--------------+--------+----------+------- (0 rows) select count(*) > 0 as ok from (select * from pg_ls_waldir() limit 1) ss; @@ -500,38 +500,38 @@ select pg_ls_dir('does not exist'); -- fails with missingok=false ERROR: could not open directory "does not exist": No such file or directory -- Check that expected columns are present select * from pg_ls_archive_statusdir() limit 0; - name | size | modification | isdir -------+------+--------------+------- + name | size | access | modification | change | creation | isdir +------+------+--------+--------------+--------+----------+------- (0 rows) select * from pg_ls_logdir() limit 0; - name | size | modification | isdir -------+------+--------------+------- + name | size | access | modification | change | creation | isdir +------+------+--------+--------------+--------+----------+------- (0 rows) select * from pg_ls_logicalmapdir() limit 0; - name | size | modification | isdir -------+------+--------------+------- + name | size | access | modification | change | creation | isdir +------+------+--------+--------------+--------+----------+------- (0 rows) select * from pg_ls_logicalsnapdir() limit 0; - name | size | modification | isdir -------+------+--------------+------- + name | size | access | modification | change | creation | isdir +------+------+--------+--------------+--------+----------+------- (0 rows) select * from pg_ls_replslotdir('') limit 0; - name | size | modification | isdir -------+------+--------------+------- + name | size | access | modification | change | creation | isdir +------+------+--------+--------------+--------+----------+------- (0 rows) select * from pg_ls_tmpdir() limit 0; - name | size | modification | isdir -------+------+--------------+------- + name | size | access | modification | change | creation | isdir +------+------+--------+--------------+--------+----------+------- (0 rows) select * from pg_ls_waldir() limit 0; - name | size | modification | isdir -------+------+--------------+------- + name | size | access | modification | change | creation | isdir +------+------+--------+--------------+--------+----------+------- (0 rows) select * from pg_stat_file('.') limit 0; @@ -542,8 +542,8 @@ select * from pg_stat_file('.') limit 0; -- This tests the missing_ok parameter, which causes pg_ls_tmpdir to succeed even if the tmpdir doesn't exist yet -- The name='' condition is never true, so the function runs to completion but returns zero rows. select * from pg_ls_tmpdir() where name='Does not exist'; - name | size | modification | isdir -------+------+--------------+------- + name | size | access | modification | change | creation | isdir +------+------+--------+--------------+--------+----------+------- (0 rows) select filename, isdir from pg_ls_dir_metadata('.') where filename='.'; @@ -559,8 +559,8 @@ select filename, isdir from pg_ls_dir_metadata('.', false, false) where filename -- Check that expected columns are present select * from pg_ls_dir_metadata('.') limit 0; - filename | size | modification | isdir -----------+------+--------------+------- + filename | size | access | modification | change | creation | isdir +----------+------+--------+--------------+--------+----------+------- (0 rows) -- diff --git a/src/test/regress/expected/tablespace.out b/src/test/regress/expected/tablespace.out index 53662fb0efd..ef64e7bfa8b 100644 --- a/src/test/regress/expected/tablespace.out +++ b/src/test/regress/expected/tablespace.out @@ -37,15 +37,15 @@ SELECT regexp_replace(pg_tablespace_location(oid), '(pg_tblspc)/(\d+)', '\1/NNN' -- The name='' condition is never true, so the function runs to completion but returns zero rows. -- The query is written to ERROR if the tablespace doesn't exist, rather than silently failing to call pg_ls_tmpdir() SELECT c.* FROM (SELECT oid FROM pg_tablespace b WHERE b.spcname='regress_tblspace' UNION SELECT 0 ORDER BY 1 DESC LIMIT 1) AS b , pg_ls_tmpdir(oid) AS c WHERE c.name='Does not exist'; - name | size | modification | isdir -------+------+--------------+------- + name | size | access | modification | change | creation | isdir +------+------+--------+--------------+--------+----------+------- (0 rows) -- This tests the missing_ok parameter. If that's not functioning, this would ERROR if the logdir doesn't exist yet. -- The name='' condition is never true, so the function runs to completion but returns zero rows. SELECT * FROM pg_ls_logdir() WHERE name='Does not exist'; - name | size | modification | isdir -------+------+--------------+------- + name | size | access | modification | change | creation | isdir +------+------+--------+--------------+--------+----------+------- (0 rows) -- try setting and resetting some properties for the new tablespace -- 2.25.1 --Pk/CTwBz1VvfPIDp-- ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Add proper planner support for ORDER BY / DISTINCT aggregates @ 2023-01-17 03:39 David Rowley <[email protected]> 2023-01-17 06:29 ` Re: Add proper planner support for ORDER BY / DISTINCT aggregates Richard Guo <[email protected]> 0 siblings, 1 reply; 5+ messages in thread From: David Rowley @ 2023-01-17 03:39 UTC (permalink / raw) To: Dean Rasheed <[email protected]>; +Cc: Tom Lane <[email protected]>; Richard Guo <[email protected]>; Ronan Dunklau <[email protected]>; Justin Pryzby <[email protected]>; Pavel Luzanov <[email protected]>; [email protected]; Ranier Vilela <[email protected]> On Tue, 17 Jan 2023 at 13:16, Dean Rasheed <[email protected]> wrote: > > On Wed, 11 Jan 2023 at 05:24, David Rowley <[email protected]> wrote: > > > > I'm wondering if 1349d279 should have just never opted to presort > > Aggrefs which have volatile functions so that the existing behaviour > > of unordered output is given always and nobody is fooled into thinking > > this works correctly only to be disappointed later when they add some > > other aggregate to their query or if we should fix both. Certainly, > > it seems much easier to do the former. > > > > I took a look at this, and I agree that the best solution is probably > to have make_pathkeys_for_groupagg() ignore Aggrefs that contain > volatile functions. Thanks for giving that some additional thought. I've just pushed a fix which adjusts things that way. David ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Add proper planner support for ORDER BY / DISTINCT aggregates 2023-01-17 03:39 Re: Add proper planner support for ORDER BY / DISTINCT aggregates David Rowley <[email protected]> @ 2023-01-17 06:29 ` Richard Guo <[email protected]> 2023-01-17 07:05 ` Re: Add proper planner support for ORDER BY / DISTINCT aggregates Tom Lane <[email protected]> 0 siblings, 1 reply; 5+ messages in thread From: Richard Guo @ 2023-01-17 06:29 UTC (permalink / raw) To: David Rowley <[email protected]>; +Cc: Dean Rasheed <[email protected]>; Tom Lane <[email protected]>; Ronan Dunklau <[email protected]>; Justin Pryzby <[email protected]>; Pavel Luzanov <[email protected]>; [email protected]; Ranier Vilela <[email protected]> On Tue, Jan 17, 2023 at 11:39 AM David Rowley <[email protected]> wrote: > On Tue, 17 Jan 2023 at 13:16, Dean Rasheed <[email protected]> > wrote: > > I took a look at this, and I agree that the best solution is probably > > to have make_pathkeys_for_groupagg() ignore Aggrefs that contain > > volatile functions. > > Thanks for giving that some additional thought. I've just pushed a > fix which adjusts things that way. This makes a lot of sense. I agree that we shouldn't do pre-sorting for volatile sort expressions, especially when there are multiple aggregates with the same volatile sort expression. Not related to this specific issue, but I find sorting by volatile expression is confusing in different scenarios. Consider the two queries given by David Query 1: select string_agg(random()::text, ',' order by random()) from generate_series(1,3); Query 2: select random()::text from generate_series(1,3) order by random(); Considering the targetlist as Aggref->args or Query->targetList, in both queries we would add an additional TargetEntry (as resjunk column) for the ORDER BY item 'random()', because it's not present in the existing targetlist. Note that the existing TargetEntry for 'random()::text' is a CoerceViaIO expression which is an explicit cast, so we cannot strip it and match it to the ORDER BY item. Thus we would have two random() FuncExprs in the final targetlist, for both queries. In query 1 we call random() twice for each tuple, one for the original TargetEntry 'random()::text', and one for the TargetEntry of the ORDER BY item 'random()', and do the sorting according to the second call results. Thus we would notice the final output is unsorted because it's from the first random() call. However, in query 2 we have the ORDER BY item 'random()' in the scan/join node's targetlist. And then for the two random() FuncExprs in the final targetlist, set_plan_references would adjust both of them to refer to the outputs of the scan/join node. Thus random() is actually called only once for each tuple and we would find the final output is sorted. It seems we fail to keep consistent about the behavior of sorting by volatile expression in the two scenarios. BTW, I wonder if we should have checked CoercionForm before fix_upper_expr_mutator steps into CoerceViaIO->arg to adjust the expr there. It seems parser checks it and only strips implicit coercions when matching TargetEntry expr to ORDER BY item. Thanks Richard ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Add proper planner support for ORDER BY / DISTINCT aggregates 2023-01-17 03:39 Re: Add proper planner support for ORDER BY / DISTINCT aggregates David Rowley <[email protected]> 2023-01-17 06:29 ` Re: Add proper planner support for ORDER BY / DISTINCT aggregates Richard Guo <[email protected]> @ 2023-01-17 07:05 ` Tom Lane <[email protected]> 2023-01-18 09:37 ` Re: Add proper planner support for ORDER BY / DISTINCT aggregates Richard Guo <[email protected]> 0 siblings, 1 reply; 5+ messages in thread From: Tom Lane @ 2023-01-17 07:05 UTC (permalink / raw) To: Richard Guo <[email protected]>; +Cc: David Rowley <[email protected]>; Dean Rasheed <[email protected]>; Ronan Dunklau <[email protected]>; Justin Pryzby <[email protected]>; Pavel Luzanov <[email protected]>; [email protected]; Ranier Vilela <[email protected]> Richard Guo <[email protected]> writes: > BTW, I wonder if we should have checked CoercionForm before > fix_upper_expr_mutator steps into CoerceViaIO->arg to adjust the expr > there. I will just quote what it says in primnodes.h: * NB: equal() ignores CoercionForm fields, therefore this *must* not carry * any semantically significant information. If you think the planner should act differently for different values of CoercionForm, you are mistaken. Maybe this is evidence of some previous bit of brain-fade, but if so we need to fix that. regards, tom lane ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Add proper planner support for ORDER BY / DISTINCT aggregates 2023-01-17 03:39 Re: Add proper planner support for ORDER BY / DISTINCT aggregates David Rowley <[email protected]> 2023-01-17 06:29 ` Re: Add proper planner support for ORDER BY / DISTINCT aggregates Richard Guo <[email protected]> 2023-01-17 07:05 ` Re: Add proper planner support for ORDER BY / DISTINCT aggregates Tom Lane <[email protected]> @ 2023-01-18 09:37 ` Richard Guo <[email protected]> 0 siblings, 0 replies; 5+ messages in thread From: Richard Guo @ 2023-01-18 09:37 UTC (permalink / raw) To: Tom Lane <[email protected]>; +Cc: David Rowley <[email protected]>; Dean Rasheed <[email protected]>; Ronan Dunklau <[email protected]>; Justin Pryzby <[email protected]>; Pavel Luzanov <[email protected]>; [email protected]; Ranier Vilela <[email protected]> On Tue, Jan 17, 2023 at 3:05 PM Tom Lane <[email protected]> wrote: > Richard Guo <[email protected]> writes: > > BTW, I wonder if we should have checked CoercionForm before > > fix_upper_expr_mutator steps into CoerceViaIO->arg to adjust the expr > > there. > > I will just quote what it says in primnodes.h: > > * NB: equal() ignores CoercionForm fields, therefore this *must* not carry > * any semantically significant information. > > If you think the planner should act differently for different values of > CoercionForm, you are mistaken. Maybe this is evidence of some > previous bit of brain-fade, but if so we need to fix that. According to this comment in primnodes.h, the planner is not supposed to treat implicit and explicit casts differently. In this case set_plan_references is doing its job correctly, to adjust both random() FuncExprs in targetlist to refer to subplan's output for query 2. As a consequence we would get a sorted output. I'm still confused that when the same scenario happens with ORDER BY in an aggregate function, like in query 1, the result is different in that we would get an unsorted output. I wonder if we should avoid this inconsistent behavior. Thanks Richard ^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2023-01-18 09:37 UTC | newest] Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2020-03-10 02:56 [PATCH v37 07/11] pg_ls_*dir to return all the metadata from pg_stat_file.. Justin Pryzby <[email protected]> 2023-01-17 03:39 Re: Add proper planner support for ORDER BY / DISTINCT aggregates David Rowley <[email protected]> 2023-01-17 06:29 ` Re: Add proper planner support for ORDER BY / DISTINCT aggregates Richard Guo <[email protected]> 2023-01-17 07:05 ` Re: Add proper planner support for ORDER BY / DISTINCT aggregates Tom Lane <[email protected]> 2023-01-18 09:37 ` Re: Add proper planner support for ORDER BY / DISTINCT aggregates Richard Guo <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox