Re: [HACKERS] First Aggregate Funtion?
>I don't think so, because arrays can contain duplicates.
I just add two element to the array. One for INITCOND value NULL, second
for first row value.
So Array size is always 2. So no duplicates.
>rhaas=# select coalesce(first(x.column1), 'wrong') from (values
>(null), ('correct')) x;
>coalesce
>--
> wrong
>(1 row)
It works correct..
I didn't said it returns, first non-null value for a column from aggregate
window.
I said my implementation returns first row value for a column.
Here first row element is "null ", hence it returns null.
check this
db=# select
db-# coalesce(first(x.column1),'null') as col1 ,
db-# coalesce(first(x.column2),'null') as col2,
db-# coalesce(first(x.column3),'null') as col3
db-# from (values (null,'abc',null), ('correct','wrong','notsure'),
('second','second1','second3')) x
db-# ;
col1 | col2 | col3
--+--+--
null | abc | null
(1 row)
Its work correct. It returns first row value for a column.
--Sudalai
-
sudalai
--
View this message in context:
http://postgresql.nabble.com/First-Aggregate-Funtion-tp1943031p5858584.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] First Aggregate Funtion?
The above implementation of "first" aggregate returns the first non-NULL item
value.
To get *first row item value* for a column use the below implementation.
-- create a function that push at most two element on given array
-- push the first row value at second index of the array
CREATE OR REPLACE FUNCTION two_value_holder(anyarray, anyelement)
returns anyarray as $$
select case when array_length($1,1) < 2 then array_append($1,$2) else
$1 end ;
$$ language sql immutable;
-- create a function that returns second element of an array
CREATE OR replace FUNCTION second_element (ANYARRAY)
RETURNS ANYELEMENT AS $$ SELECT $1[2]; $$ LANGUAGE SQL;
-- create first aggregate function that return first_row item value
CREATE AGGREGATE first(anyelement)(
SFUNC=two_value_holder,
STYPE=ANYARRAY,
INITCOND='{NULL}',
FINALFUNC=second_element
);
I hope this work..
--
Sudalai
-
sudalai
--
View this message in context:
http://postgresql.nabble.com/First-Aggregate-Funtion-tp1943031p5857866.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Re: File count restriction of directory limits number of relations inside a database.
Hi, Ya you are right, ext4 allows more directory entries(more than 32000) but we limited the number of files insides the directory to 32000 to get better performance. Sorry i'm not mentioned that in my post. That the reason we plan to use tablespace. The problem we faced in tablespace is, the location should be present on both master and slave and we need to create multiple tablespaces. That why i changed the source, to create a sub directory on the given location and take that location for tablespace creation. So i can given one location (that present in both master & slave) to create multiple tablespaces. - sudalai -- View this message in context: http://postgresql.nabble.com/File-count-restriction-of-directory-limits-number-of-relations-inside-a-database-tp5844711p5845044.html Sent from the PostgreSQL - hackers mailing list archive at Nabble.com. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] File count restriction of directory limits number of relations inside a database.
(errcode(ERRCODE_OBJECT_IN_USE),
errmsg("directory \"%s\" already in use as a
tablespace",
location_with_version_dir)));
else
ereport(ERROR,
(errcode_for_file_access(),
errmsg("could not create directory \"%s\": %m",
location_with_version_dir)));
}
/*
* In recovery, remove old symlink, in case it points to the
wrong place.
*
* On Windows, junction points act like directories so we must
be able to
* apply rmdir; in general it seems best to make this code work
like the
* symlink removal code in destroy_tablespace_directories,
except that
* failure to remove is always an ERROR.
*/
if (InRecovery)
{
if (lstat(linkloc, &st) == 0 && S_ISDIR(st.st_mode))
{
if (rmdir(linkloc) < 0)
ereport(ERROR,
(errcode_for_file_access(),
errmsg("could not remove directory \"%s\":
%m",
linkloc)));
}
else
{
if (unlink(linkloc) < 0 && errno != ENOENT)
ereport(ERROR,
(errcode_for_file_access(),
errmsg("could not remove symbolic link
\"%s\": %m",
linkloc)));
}
}
/*
* Create the symlink under PGDATA
*/
if (symlink(newlocation, linkloc) < 0)
ereport(ERROR,
(errcode_for_file_access(),
errmsg("could not create symbolic link \"%s\": %m",
linkloc)));
pfree(linkloc);
pfree(newlocation);
pfree(location_with_version_dir);
}
-
sudalai
--
View this message in context:
http://postgresql.nabble.com/File-count-restriction-of-directory-limits-number-of-relations-inside-a-database-tp5844711.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Dynamically change Master(recovery info) without restarting standby server..
Hi, Is there any way to change the *master* without restarting the *standby* server. Postgresql Documentation says, -->Recovery.conf file only read on the startup in standby mode. In that file we specify the "Masterip". -->If you want to change the master we need to change the recovery.conf file and restart the standby node. but, *I Need to change masterip without restarting the standby node.* - sudalai -- View this message in context: http://postgresql.1045698.n5.nabble.com/Dynamically-change-Master-recovery-info-without-restarting-standby-server-tp5824423.html Sent from the PostgreSQL - hackers mailing list archive at Nabble.com. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Master ip from hot_standby..
Hi, I need to query master ip from hot_standby. *pg_stat_replication* view only shows the slave replication status. Is there any way to get *Master IP* from standby node apart from checking *recovery.conf* file. Thanks, Sudalai - sudalai -- View this message in context: http://postgresql.1045698.n5.nabble.com/Master-ip-from-hot-standby-tp5824415.html Sent from the PostgreSQL - hackers mailing list archive at Nabble.com. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
