Re: [GENERAL] How to convert a double value to a numeric datum type in pgsql?
2014-02-24 8:42 GMT+01:00 Felix.徐 : > Thanks , I find another function called OidInputFunctionCall which can > automatically generate a datum from a string for any data type, but seems > it is not recommended in the comments. > it is little bit different - and it needs a second conversion - double -> string Regards Pavel > > > 2014-02-24 15:32 GMT+08:00 Pavel Stehule : > > >> hello >> >> you have to look to postgresql/src/backend/utils/adt/numeric.c functions >> - >> >> then you can call float8_numeric >> >> Numeric result = DatumGetNumeric(DirectFunctionCall1(float8_numeric, >> Float8GetDatum(dx)); >> >> regards >> >> Pavel >> >> >> 2014-02-24 7:45 GMT+01:00 Felix.徐 : >> >>> Hi all, >>> I'm talking about the source code of pgsql and I want to know how the >>> typing system works in pgsql. >>> A few functions can help us do the type conversion, for example: >>> >>> Int32GetDatum -- convert a integer to a datum >>> Float8GetDatum -- convert double to a datum >>> cstring_to_text -- convert a string to a text >>> ... >>> >>> but there are a lot of types in pgsql,how to choose the corresponding >>> function? or is there a more automatic way to do the type conversion(raw >>> chars to a datum, suppose I have the corresponding Form_pg_type instance)? >>> >>> I ask this question because I don't know how to convert a double value >>> to a numeric datum(the field's type is numeric(10,2)), pg_type shows that >>> numeric's typlen is -1 whose length is variable thus Float8GetDatum is not >>> working.. >>> >>> >>> >>> >>> >> >
Re: [GENERAL] How to convert a double value to a numeric datum type in pgsql?
hello you have to look to postgresql/src/backend/utils/adt/numeric.c functions - then you can call float8_numeric Numeric result = DatumGetNumeric(DirectFunctionCall1(float8_numeric, Float8GetDatum(dx)); regards Pavel 2014-02-24 7:45 GMT+01:00 Felix.徐 : > Hi all, > I'm talking about the source code of pgsql and I want to know how the > typing system works in pgsql. > A few functions can help us do the type conversion, for example: > > Int32GetDatum -- convert a integer to a datum > Float8GetDatum -- convert double to a datum > cstring_to_text -- convert a string to a text > ... > > but there are a lot of types in pgsql,how to choose the corresponding > function? or is there a more automatic way to do the type conversion(raw > chars to a datum, suppose I have the corresponding Form_pg_type instance)? > > I ask this question because I don't know how to convert a double value to > a numeric datum(the field's type is numeric(10,2)), pg_type shows that > numeric's typlen is -1 whose length is variable thus Float8GetDatum is not > working.. > > > > >
Re: [GENERAL] How to continue streaming replication after this error?
On Mon, Feb 24, 2014 at 12:23 PM, Torsten Förtsch wrote: > On 22/02/14 03:21, Torsten Förtsch wrote: > > Any idea what that means? > > Updating the replica to 9.3.3 cured it. The master was already on 9.3.3. > 9.3.3 has introduced some new configuration parameters. So you need to actually update a slave before the master or replication is broken. -- Michael
[GENERAL] How to convert a double value to a numeric datum type in pgsql?
Hi all, I'm talking about the source code of pgsql and I want to know how the typing system works in pgsql. A few functions can help us do the type conversion, for example: Int32GetDatum -- convert a integer to a datum Float8GetDatum -- convert double to a datum cstring_to_text -- convert a string to a text ... but there are a lot of types in pgsql,how to choose the corresponding function? or is there a more automatic way to do the type conversion(raw chars to a datum, suppose I have the corresponding Form_pg_type instance)? I ask this question because I don't know how to convert a double value to a numeric datum(the field's type is numeric(10,2)), pg_type shows that numeric's typlen is -1 whose length is variable thus Float8GetDatum is not working..
Re: [GENERAL] Why does PostgreSQL ftruncate before unlink?
Jon Nelson writes: > On Sun, Feb 23, 2014 at 9:49 PM, Tom Lane wrote: >> If memory serves, the inode should get removed during the next checkpoint. > I was moments away from commenting to say that I had traced the flow > of the code to md.c and found the comments there quite illuminating. I > wonder if there is a different way to solve the underlying issue > without relying on ftruncate (which seems to be somewhat expensive). Hm. The code is designed the way it is on the assumption that ftruncate doesn't do anything that unlink wouldn't have to do anyway. If it really is significantly slower on popular filesystems, maybe we need to revisit that. 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] Why does PostgreSQL ftruncate before unlink?
On Sun, Feb 23, 2014 at 9:49 PM, Tom Lane wrote: > Jeff Janes writes: >> On Sunday, February 23, 2014, Scott Marlowe wrote: >>> I'm guessing that this is so that it can be rolled back. Unlink is >>> likely issued at commit; > >> I would hope that ftruncate is issued at commit as well. That doesn't >> sound undoable. > > It's more subtle than that. I'm too lazy to look at the comments in md.c > right now, but basically the reason for not doing an instant unlink is > to ensure that if a relation is truncated and then re-extended, open file > pointers held by other backends will still be valid. The ftruncate is > done to ensure that allocated disk space goes away as soon as that's safe > (ie, at commit of the truncation); but immediate unlink would require > forcing more cross-backend synchronization than we want to have. > > If memory serves, the inode should get removed during the next checkpoint. I was moments away from commenting to say that I had traced the flow of the code to md.c and found the comments there quite illuminating. I wonder if there is a different way to solve the underlying issue without relying on ftruncate (which seems to be somewhat expensive). -- Jon -- 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] Why does PostgreSQL ftruncate before unlink?
Jeff Janes writes: > On Sunday, February 23, 2014, Scott Marlowe wrote: >> I'm guessing that this is so that it can be rolled back. Unlink is >> likely issued at commit; > I would hope that ftruncate is issued at commit as well. That doesn't > sound undoable. It's more subtle than that. I'm too lazy to look at the comments in md.c right now, but basically the reason for not doing an instant unlink is to ensure that if a relation is truncated and then re-extended, open file pointers held by other backends will still be valid. The ftruncate is done to ensure that allocated disk space goes away as soon as that's safe (ie, at commit of the truncation); but immediate unlink would require forcing more cross-backend synchronization than we want to have. If memory serves, the inode should get removed during the next checkpoint. 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] Why does PostgreSQL ftruncate before unlink?
On Sunday, February 23, 2014, Scott Marlowe wrote: > On Fri, Feb 21, 2014 at 4:14 PM, Jon Nelson > > > wrote: > > When dropping lots of tables, I noticed postgresql taking longer than > > I would have expected. > > > > strace seems to report that the largest contributor is the ftruncate > > and not the unlink. I'm curious what the logic is behind using > > ftruncate before unlink. > > > > I'm using an ext4 filesystem. > > I'm guessing that this is so that it can be rolled back. Unlink is > likely issued at commit; > I would hope that ftruncate is issued at commit as well. That doesn't sound undoable. Cheers, Jeff
Re: [GENERAL] How to continue streaming replication after this error?
On 22/02/14 03:21, Torsten Förtsch wrote: >> 2014-02-21 05:17:10 UTC PANIC: heap2_redo: unknown op code 32 >> > 2014-02-21 05:17:10 UTC CONTEXT: xlog redo UNKNOWN >> > 2014-02-21 05:17:11 UTC LOG: startup process (PID 1060) was terminated >> > by signal 6: Aborted >> > 2014-02-21 05:17:11 UTC LOG: terminating any other active server processes >> > 2014-02-21 05:17:11 UTC WARNING: terminating connection because of >> > crash of another server process >> > 2014-02-21 05:17:11 UTC DETAIL: The postmaster has commanded this >> > server process to roll back the current transaction and exit, because >> > another server process exited abnormally and possibly corrupted shared >> > memory. >> > 2014-02-21 05:17:11 UTC HINT: In a moment you should be able to >> > reconnect to the database and repeat your command. > Any idea what that means? Updating the replica to 9.3.3 cured it. The master was already on 9.3.3. Torsten -- 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] Why does PostgreSQL ftruncate before unlink?
On Fri, Feb 21, 2014 at 4:14 PM, Jon Nelson wrote: > When dropping lots of tables, I noticed postgresql taking longer than > I would have expected. > > strace seems to report that the largest contributor is the ftruncate > and not the unlink. I'm curious what the logic is behind using > ftruncate before unlink. > > I'm using an ext4 filesystem. I'm guessing that this is so that it can be rolled back. Unlink is likely issued at commit; -- 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 continue streaming replication after this error?
On Sat, Feb 22, 2014 at 1:21 PM, Torsten Förtsch wrote: > On 21/02/14 09:17, Torsten Förtsch wrote: > > one of our streaming replicas died with > > > > 2014-02-21 05:17:10 UTC PANIC: heap2_redo: unknown op code 32 > > 2014-02-21 05:17:10 UTC CONTEXT: xlog redo UNKNOWN > > 2014-02-21 05:17:11 UTC LOG: startup process (PID 1060) was terminated > > by signal 6: Aborted > > 2014-02-21 05:17:11 UTC LOG: terminating any other active server > processes > > 2014-02-21 05:17:11 UTC WARNING: terminating connection because of > > crash of another server process > > 2014-02-21 05:17:11 UTC DETAIL: The postmaster has commanded this > > server process to roll back the current transaction and exit, because > > another server process exited abnormally and possibly corrupted shared > > memory. > > 2014-02-21 05:17:11 UTC HINT: In a moment you should be able to > > reconnect to the database and repeat your command. > > Any idea what that means? > > I have got a second replica dying with the same symptoms. The Xlog record seems to be corrupted. The op code 32 represents XLOG_HEAP2_FREEZE_PAGE, the code exists to handle it. Don't know why the system is not able to recognize the op code? Can you try pg_xlogdump of the corrupted WAL file? Keep the data folder for problem investigation. As it seems some of kind corruption, you need to take a fresh base backup to continue. Regards, Hari Babu Fujitsu Australia
Re: [GENERAL] sum from table 1, where from table 2
On 02/23/2014 10:12 AM, John Smith wrote: Testing Stefans theory about formatting of email. test=> select version(); version - PostgreSQL 9.3.2 on i686-pc-linux-gnu, compiled by gcc (SUSE Linux) 4.7.1 20120723 [gcc-4_7-branch revision 189773], 32-bit (1 row) Yes I know I need to upgrade. Well trying the above I get: test=> select sum(vote.total), st from public.vote, public.state where vote.city = state.city group by st order by sum(vote.total) desc; sum | st -+ 85 | ca 11 | ny (2 rows) To get the column order you show I did: test=>select st, sum(vote.total) from public.vote, public.state where vote.city = state.city group by st order by sum(vote.total) desc; st | sum +- ca | 85 ny | 11 (2 rows) So I am not sure what is not working on your end? -- Adrian Klaver adrian.kla...@aklaver.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] Test
On 02/23/2014 10:57 AM, Adrian Klaver wrote: I tried sending email reply to this list and it was flagged as spam. Just testing Thanks to quick response from Stefan Kaltenbrunner, the problem has been identified. I was responding to John Smiths email and it turns out that tripped the spam filter. Would seem state (dot) st as a column name in its unadulterated state is the same as a known black listed site. My reformatting of the query seemed to have pushed the spam filter into a decision. Just in case anyone gets bit by this. Thanks again to the community out there, keeping an eye on things. Thanks, -- Adrian Klaver adrian.kla...@aklaver.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Test
I tried sending email reply to this list and it was flagged as spam. Just testing Thanks, -- Adrian Klaver adrian.kla...@aklaver.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] sum from table 1, where from table 2
it works (diego != deigo). thanks, jzs -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] sum from table 1, where from table 2
i have two tables, like so: 1. create table public.vote ( sn integer primary key, total integer, cityvarchar(24) ); sn | total | city 1 | 11| new york 2 | 27| los angeles 3 | 58| san diego 2. create table public.state ( sn integer primary key, st varchar(2), cityvarchar(24) ); sn | st| city 1 | ny| new york 2 | ca| los angeles 3 | ca| san deigo i am looking for a result, like so: st | total ca | 85 ny | 11 but this doesn't work: select sum(vote.total), state.st frompublic.vote, public.state where vote.city = state.city group bystate.st order bysum(vote.total) desc; subquery? having? thanks, jzs -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
