Re: psycopg2.DataError: invalid input syntax for integer: ""

[tango ward]

I think I've found the culprit of the problem.

I have a field which is varchar from the source DB while on the destination
DB its integer.

Reading the documentation:
http://www.postgresqltutorial.com/postgresql-cast/ but it gives me error
`psycopg2.DataError: invalid input syntax for integer: ""`

On Mon, May 7, 2018 at 2:39 PM, David G. Johnston <
david.g.johns...@gmail.com> wrote:

> On Sunday, May 6, 2018, tango ward  wrote:
>
>> Yes, my apologies.
>>
>> May I also ask if there's a limitation for the number of timestamp with
>> timezone fields in a table?
>>
>
> Not one that is likely to matter in practice.  There's a page discussing
> limitations on the website/docs somewhere if you wish to find out more.
>
> David J.
>

Is it possible to get username information while writing trigger?

[a]

Hey:


What I want is to add a log entry at the last column of each row, which will 
record the history update, insert automatically when relative statement is 
processed.


I have read the documentation on triggers, which helps a lot. However, I may 
have few more extra requirement to complete my wishes:


1, I would like to get the username of who executed the statement;


2, I would like to get the column name that is being updated;


If it is possible and how should I do it??


Thanks a lot!

Re: Is it possible to get username information while writing trigger?

[Laurenz Albe]

a wrote:
> What I want is to add a log entry at the last column of each row, which will 
> record the
> history update, insert automatically when relative statement is processed.
> 
> I have read the documentation on triggers, which helps a lot. However, I may 
> have few
> more extra requirement to complete my wishes:
> 
> 1, I would like to get the username of who executed the statement;
> 
> 2, I would like to get the column name that is being updated;
> 
> If it is possible and how should I do it??

You could use the "current_user" function to get the current user.

Mind, however, that updates caused by a cascading update from a
foreign key constraint will be executed as the owner of the table,
so it would be better to use "session_user" to avoid surprises.

You cannot get the column name, because PostgreSQL updates a whole row,
not an individual column. The best you can do is to check which
column values are different in OLD and NEW.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com

Re: Is it possible to get username information while writingtrigger?

[a]

Thank you for your reply;


Please allow me to ask few more questions:


1, Since I'm writing a C trigger function, is there any method for me to get 
some of the basic information like the follow:


 (1) Total number of rows;
 (2) Rows' names;
 (3) Value of OLD and NEW;


2, Is there any possibility of passing the SQL statement it self into the 
trigger?


3, Is it possible for me to exam before statement trigger so that I would be 
able to loop it once and copying the update information to the rest of rows.


Thanks a lot!




-- Original --
From:  "Laurenz Albe";;
Send time: Monday, May 7, 2018 3:57 PM
To: "a"<372660...@qq.com>; "pgsql-general"; 

Subject:  Re: Is it possible to get username information while writingtrigger?



a wrote:
> What I want is to add a log entry at the last column of each row, which will 
> record the
> history update, insert automatically when relative statement is processed.
> 
> I have read the documentation on triggers, which helps a lot. However, I may 
> have few
> more extra requirement to complete my wishes:
> 
> 1, I would like to get the username of who executed the statement;
> 
> 2, I would like to get the column name that is being updated;
> 
> If it is possible and how should I do it??

You could use the "current_user" function to get the current user.

Mind, however, that updates caused by a cascading update from a
foreign key constraint will be executed as the owner of the table,
so it would be better to use "session_user" to avoid surprises.

You cannot get the column name, because PostgreSQL updates a whole row,
not an individual column. The best you can do is to check which
column values are different in OLD and NEW.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com

Re: Is it possible to get username information while writingtrigger?

[Laurenz Albe]

a wrote:
> Please allow me to ask few more questions:
> 
> 1, Since I'm writing a C trigger function, is there any method for me to get 
> some of the
>basic information like the follow:
> 
>  (1) Total number of rows;
>  (2) Rows' names;
>  (3) Value of OLD and NEW;
> 
> 2, Is there any possibility of passing the SQL statement it self into the 
> trigger?
> 
> 3, Is it possible for me to exam before statement trigger so that I would be 
> able to loop it
>once and copying the update information to the rest of rows.

I don't know if there is a reliable way to get the SQL statement from
a C trigger.

For the other things, perhaps a statement level trigger with transition
relations can help.

You can then access the transition relations from your C code with
the tg_oldtable and tg_newtable tuplestores.
(https://www.postgresql.org/docs/current/static/trigger-interface.html)

Yours,
Laurenz Albe
-- 
+43-670-6056265
Cybertec Schönig & Schönig GmbH
Gröhrmühlgasse 26, A-2700 Wiener Neustadt
Web: https://www.cybertec-postgresql.com

Re: Query planner riddle (array-related?)

[Markus]

Hi Tom,

On Fri, May 04, 2018 at 09:32:08AM -0400, Tom Lane wrote:
> Markus  writes:
> > I'm running Postgresql 9.6.7 on Debian stretch -- and I'm trying to
> > understand a query plan, with any hint where to gain further insight
> > welcome.
> 
> Well, you say
> 
> >   select count(*) from gaia.dr2light where parallax>50;
> > gives 5400 rows in no time.
> 
> but the planner thinks there are 12991627 such rows:

Ah... yeah, the parallax distribution is fairly sharply peaked around
0, so >50 might be severely off.

So, I've run

  alter table gaia.dr2light alter parallax set statistics 1000;
  analyze gaia.dr2light;

and lo and behold, the both queries become a good deal faster (a
couple of seconds).  That's essentially enough to make me happy --
thanks!

> Also, this sort of thing is usually much easier to diagnose from
> EXPLAIN ANALYZE output.  All we can see from these queries is that
> the planner picked what it thought was the lowest-cost plan.  Without
> actual rowcounts it's very hard to guess why the estimates were wrong.
> You happened to provide one actual-rowcount number that maybe was
> enough to diagnose the issue; but if the above doesn't do the trick,
> we're going to need to see EXPLAIN ANALYZE to guess what else is up.

With this, the query plans converge to trivial variations of

 Hash Join  (cost=253856.23..4775113.84 rows=422 width=1647) (actual 
time=1967.095..2733.109 rows=18 loops=1)
   Hash Cond: (dr2light.source_id = dr2epochflux.source_id)
   ->  Bitmap Heap Scan on dr2light  (cost=24286.88..4385601.28 rows=1297329 
width=132) (actual time=3.113..19.346 rows=5400 loops=1)
 Recheck Cond: (parallax > '50'::double precision)
 Heap Blocks: exact=5184
 ->  Bitmap Index Scan on dr2light_parallax  (cost=0.00..23962.54 
rows=1297329 width=0) (actual time=1.721..1.721 rows=5400 loops=1)
   Index Cond: (parallax > '50'::double precision)
   ->  Hash  (cost=118285.38..118285.38 rows=551038 width=1523) (actual 
time=1885.177..1885.177 rows=550737 loops=1)
 Buckets: 65536  Batches: 16  Memory Usage: 53292kB
 ->  Seq Scan on dr2epochflux  (cost=0.00..118285.38 rows=551038 
width=1523) (actual time=0.008..430.692 rows=550737 loops=1)
 Planning time: 6.504 ms
 Execution time: 2733.653 ms

(with 10% or so jitter in the actual times, obviously); this one is
for

  SELECT *
  FROM gaia.dr2epochflux
  JOIN gaia.dr2light
  USING (source_id)
  WHERE parallax>50


While that's a reasonable plan and fast enough, I'd still like to
keep the box from seqscanning dr2epochflux with its large arrays and
use that table's index on source_id.  If I read the query plan right,
part of the problem is that it still massively overestimates the
result of parallax>50 (1297329 rather than 5400).  Is there anything
I can do to improve that estimate?

But even with that suboptimal estimate, postgres, under the
assumption of something not too far from a uniform distribution on
source_id, should end up estimating the cardinality of the end result
as something like

(selectivity on dr2light)*(cardinality of dr2epochflux),

and hence roughly (1297329/1.6e9*5e5)=405 rows to be drawn from
dr2epochflux.  It would seem a lot smarter to just pull these few 1e2
rows using the source_id index on dr2epochflux than seqscanning that
table, no?

Btw., I've raised the statistics target on dr2light to 1000 for
source_id; so, postgres should know source_id isn't uniformly
distributed, but it should also see it's not *that* far away from it.


   -- Markus

Re: postgres on physical replica crashes

[SRINIVASARAO OGURI]

Hi Greig Wise,

If you are using CentOS/REDHAT - 07 , check this link "
https://srinivasoguri.blogspot.in/2018/04/postgresql-crash-in-centosredhat-07.html
"

On Fri, Apr 20, 2018 at 6:58 PM, Hannes Erven  wrote:

> Hi Greig,
>
>
> just last week I experienced the same situation as you on a 10.3 physical
> replica (it even has checksums activated), and a few months ago on 9.6 .
> We used the same resolution as you we, and so far we haven't noticed any
> problems with data integrity on the replicas.
>
>
>
> The logs were as follows:
> 2018-04-13 06:31:16.947 CEST [15603] FATAL:  WAL-Receiver-Prozess wird
> abgebrochen wegen Zeitüberschreitung
> 2018-04-13 06:31:16.948 CEST [15213] FATAL:  invalid memory alloc request
> size 4280303616
> 2018-04-13 06:31:16.959 CEST [15212] LOG:  Startprozess (PID 15213)
> beendete mit Status 1
> 2018-04-13 06:31:16.959 CEST [15212] LOG:  aktive Serverprozesse werden
> abgebrochen
> 2018-04-13 06:31:16.959 CEST [19838] user@db WARNUNG:  Verbindung wird
> abgebrochen wegen Absturz eines anderen Serverprozesses
> 2018-04-13 06:31:16.959 CEST [19838] user@db DETAIL:  Der Postmaster hat
> diesen Serverprozess angewiesen, die aktuelle Transaktion zurückzurollen
> und die Sitzung zu beenden, weil ein anderer Serverprozess abnormal beendet
> wurde und möglicherweise das Shared Memory verfälscht hat.
> 2018-04-13 06:31:16.959 CEST [19838] user@db TIPP:  In einem Moment
> sollten Sie wieder mit der Datenbank verbinden und Ihren Befehl wiederholen
> können.
>
>
> This replica then refused to start up:
> 2018-04-13 09:25:15.941 CEST [1957] LOG:  Standby-Modus eingeschaltet
> 2018-04-13 09:25:15.947 CEST [1957] LOG:  Redo beginnt bei 1C/69C0FF30
> 2018-04-13 09:25:15.951 CEST [1957] LOG:  konsistenter
> Wiederherstellungszustand erreicht bei 1C/69D9A9C0
> 2018-04-13 09:25:15.952 CEST [1956] LOG:  Datenbanksystem ist bereit, um
> lesende Verbindungen anzunehmen
> 2018-04-13 09:25:15.953 CEST [1957] FATAL:  invalid memory alloc request
> size 4280303616
> 2018-04-13 09:25:15.954 CEST [1956] LOG:  Startprozess (PID 1957) beendete
> mit Status 1
>
>
> ... until the WAL files from the hot standby's pg_wal were manually
> removed and re-downloaded from the primary.
>
> Unfortunately I did not collect hard evidence, but I think I saw the
> primary's replication slot's restart point was set to a position /after/
> the standby's actual restart location. This time, the error was noticed
> immediately and the required WAL was still present on the master.
>
>
> A few months ago I experienced the same situation on a 9.6 cluster, but
> that was not noticed for a long time, and - despite using a replication
> slot! - the primary had already removed required segments. Fortunately I
> could get them from a tape backup...
>
>
>
> Best regards,
>
> -hannes
>
>
>
>
> Am 2018-04-18 um 18:16 schrieb greigwise:
>
>> Hello.  I've had several instances where postgres on my physical replica
>> under version 9.6.6 is crashing with messages like the following in the
>> logs:
>>
>> 2018-04-18 05:43:26 UTC dbname 5acf5e4a.6918 dbuser 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.
>> 2018-04-18 05:43:26 UTC dbname 5acf5e4a.6918 dbuser HINT:  In a moment you
>> should be able to reconnect to the database and repeat your command.
>> 2018-04-18 05:43:26 UTC dbname 5acf5e39.68e5 dbuser WARNING:  terminating
>> connection because of crash of another server process
>> 2018-04-18 05:43:26 UTC dbname 5acf5e39.68e5 dbuser 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.
>> 2018-04-18 05:43:26 UTC dbname 5acf5e39.68e5 dbuser HINT:  In a moment you
>> should be able to reconnect to the database and repeat your command.
>> 2018-04-18 05:43:27 UTC  5acf5e12.6819  LOG:  database system is shut down
>>
>> When this happens, what I've found is that I can go into the pg_xlog
>> directory on the replica, remove all the log files and the postgres will
>> restart and things seem to come back up normally.
>>
>> So, the question is what's going on here... is the log maybe getting
>> corrupt
>> in transmission somehow?  Should I be concerned about the viability of my
>> replica after having restarted in the described fashion?
>>
>> Thanks,
>> Greig Wise
>>
>>
>>
>> --
>> Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f184378
>> 0.html
>>
>>
>
>

Re: Add schema to the query

[Igor Korot]

Rob,

On Sun, May 6, 2018 at 11:54 PM, Rob Sargent  wrote:
>
>
>> On May 6, 2018, at 8:19 PM, Igor Korot  wrote:
>>
>> Hi, ALL,
>>
>> Is there an easy way to add the schema to the following query:
>>
>> SELECT u.usename FROM pg_class c, pg_user u WHERE u.usesysid =
>> c.relowner AND relname = ?
>>
>> Thank you.
>>
> Are you looking for the owner of a particular schema.tablename?

Yes, I am.

That;s what I ended up with:

SELECT u.usename FROM pg_class c, pg_user u, pg_namespace n WHERE
n.oid = c.relnamespace AND u.usesysid = c.relowner AND n.nspname = ?
AND relname = ?

Thank you.

Re: psycopg2.DataError: invalid input syntax for integer: ""

[David G. Johnston]

On Mon, May 7, 2018 at 12:28 AM, tango ward  wrote:

> I think I've found the culprit of the problem.
>
> I have a field which is varchar from the source DB while on the
> destination DB its integer.
>
> Reading the documentation: http://www.postgresqltutorial.
> com/postgresql-cast/ but it gives me error `psycopg2.DataError: invalid
> input syntax for integer: ""`
>
>
You haven't asked a question and your statement is unsurprising to me.  Are
you good now or do you still harbor confusion?

David J.
​

Re: psycopg2.DataError: invalid input syntax for integer: ""

[Adrian Klaver]

On 05/06/2018 11:05 PM, tango ward wrote:

Yes, my apologies.

May I also ask if there's a limitation for the number of timestamp with 
timezone fields in a table?


On Mon, May 7, 2018 at 1:37 PM, David G. Johnston 
mailto:david.g.johns...@gmail.com>> wrote:


Please keep replies on-list, don't top-post, and double-check that
the database table doesn't somehow have an integer column where you
think its text.


On Sunday, May 6, 2018, tango ward mailto:tangowar...@gmail.com>> wrote:

Yeah, the error doesn't really explain much. I have tried
putting the string formatter in ' ', still no good.

On Mon, May 7, 2018 at 12:14 PM, David G. Johnston
mailto:david.g.johns...@gmail.com>>
wrote:

On Sunday, May 6, 2018, tango ward mailto:tangowar...@gmail.com>> wrote:

Hi,

There's a mistake in the code, my bad.

I updated the code into

|cur_p.execute(""" INSERT INTO a_recipient (created, mod,
agreed, address, honor) VALUES (current_timestamp,
current_timestamp, current_timestamp, %s, %s)""",('', ''))



|

The code still won't work. The address and honor fields
are textfields in Django models.py. 



Not sure but I'm thinking you at least need to add single
quotes around the %s symbols.  That doesn't really explain
the integer input error though I'm not familiar with the
exact features of the execute method in Python.


They do not need to be quoted:

http://initd.org/psycopg/docs/usage.html#passing-parameters-to-sql-queries



David J.






--
Adrian Klaver
adrian.kla...@aklaver.com

Re: psycopg2.DataError: invalid input syntax for integer: ""

[David G. Johnston]

On Mon, May 7, 2018 at 6:35 AM, Adrian Klaver 
wrote:

>
>> Not sure but I'm thinking you at least need to add single
>> quotes around the %s symbols.  That doesn't really explain
>> the integer input error though I'm not familiar with the
>> exact features of the execute method in Python.
>>
>
> They do not need to be quoted:
>
> http://initd.org/psycopg/docs/usage.html#passing-parameters-to-sql-queries


​Yeah, upon further reflection overnight I figured it must incorporate
sql-injection prevention.  The use of "%s", which is typically a printf
construct and printf doesn't do that kind of thing, threw me.

David J.

Re: psycopg2.DataError: invalid input syntax for integer: ""

[Adrian Klaver]

On 05/07/2018 12:28 AM, tango ward wrote:

I think I've found the culprit of the problem.

I have a field which is varchar from the source DB while on the 
destination DB its integer.


Reading the documentation: 
http://www.postgresqltutorial.com/postgresql-cast/ but it gives me error 
`psycopg2.DataError: invalid input syntax for integer: ""`


Would need to see your code to be sure, but I am gong to guess you are 
trying to CAST the string to integer in the SQL e.g CAST(some_str_value 
AS INTEGER) or some_str_value::integer. The error you are getting is :


test=# select CAST('' AS INTEGER);
ERROR:  invalid input syntax for integer: ""
LINE 1: select CAST('' AS INTEGER);
^
test=# select ''::integer;
ERROR:  invalid input syntax for integer: ""
LINE 1: select ''::integer;

Two options:

1) You will need the catch the '' on the Python side before they get to 
the database and turn them into None(if Nulls allowed in column) or 0 
otherwise.


2) If possible convert the integer column on the destination db to a 
varchar one. Though I would do some investigation before doing this as 
this may very well mess up other code.




On Mon, May 7, 2018 at 2:39 PM, David G. Johnston 
mailto:david.g.johns...@gmail.com>> wrote:


On Sunday, May 6, 2018, tango ward mailto:tangowar...@gmail.com>> wrote:

Yes, my apologies.

May I also ask if there's a limitation for the number of
timestamp with timezone fields in a table?


Not one that is likely to matter in practice.  There's a page
discussing limitations on the website/docs somewhere if you wish to
find out more.

David J.





--
Adrian Klaver
adrian.kla...@aklaver.com

void function and view select

[Philipp Kraus]

Hello,

I have got a complex query with a dynamic column result e.g.:

select builddata('_foo‘);
select * from _foo;

The first is a plsql function which creates a temporary table, but the function 
returns void.
The second call returns all the data from this table. But the columns of the 
temporary table are
not strict fixed, so I cannot return a table by the function. 
So my question is, how can I build with this two lines a view, so that I can 
run "select * from myFooView“ or
a function with a dynamic return set of columns e.g. „select myFoo()“?

Thanks

Phil

Re: Is it possible to get username information while writingtrigger?

[Adrian Klaver]

On 05/07/2018 01:39 AM, a wrote:

Thank you for your reply;

Please allow me to ask few more questions:

1, Since I'm writing a C trigger function, is there any method for me to 
get some of the basic information like the follow:


      (1) Total number of rows;
      (2) Rows' names;
      (3) Value of OLD and NEW;

2, Is there any possibility of passing the SQL statement it self into 
the trigger?


3, Is it possible for me to exam before statement trigger so that I 
would be able to loop it once and copying the update information to the 
rest of rows.


Transition table(s):

https://www.postgresql.org/docs/10/static/sql-createtrigger.html

This is new to version 10 and I have not actually used this feature yet, 
so all I can do is point you at the docs.




Thanks a lot!


-- Original --
*From: * "Laurenz Albe";;
*Send time:* Monday, May 7, 2018 3:57 PM
*To:* "a"<372660...@qq.com>; "pgsql-general";
*Subject: * Re: Is it possible to get username information while 
writingtrigger?


a wrote:
 > What I want is to add a log entry at the last column of each row, 
which will record the
 > history update, insert automatically when relative statement is 
processed.

 >
 > I have read the documentation on triggers, which helps a lot. 
However, I may have few

 > more extra requirement to complete my wishes:
 >
 > 1, I would like to get the username of who executed the statement;
 >
 > 2, I would like to get the column name that is being updated;
 >
 > If it is possible and how should I do it??

You could use the "current_user" function to get the current user.

Mind, however, that updates caused by a cascading update from a
foreign key constraint will be executed as the owner of the table,
so it would be better to use "session_user" to avoid surprises.

You cannot get the column name, because PostgreSQL updates a whole row,
not an individual column. The best you can do is to check which
column values are different in OLD and NEW.

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com



--
Adrian Klaver
adrian.kla...@aklaver.com

Re: void function and view select

[David G. Johnston]

On Mon, May 7, 2018 at 6:52 AM, Philipp Kraus  wrote:

> Hello,
>
> I have got a complex query with a dynamic column result e.g.:
>
> select builddata('_foo‘);
> select * from _foo;
>
> The first is a plsql function which creates a temporary table, but the
> function returns void.
> The second call returns all the data from this table. But the columns of
> the temporary table are
> not strict fixed, so I cannot return a table by the function.
> So my question is, how can I build with this two lines a view, so that I
> can run "select * from myFooView“ or
> a function with a dynamic return set of columns e.g. „select myFoo()“?
>

​Executed queries must have a well-defined column structure at
parse/plan-time, execution cannot change the columns that are returned.

By extension, a view's column structure must be stable.  Writing:

CREATE VIEW v1 AS
SELECT * FROM tbl1;

Causes the view to defined with all columns of tbl1 as known at the time of
the view's creation (i.e., * is expanded immediately).

You might be able to use cursors to accomplish whatever bigger goal you are
working toward (I'm not particularly fluent with this technique).

​The more direct way to accomplish this is:

SELECT *
FROM func_call() AS (col1 text, col2 int, col3 date)

i.e., have the function return "SETOF record" and then specify the format
of the returned record when calling the function.

David J.

Re: void function and view select

[Philipp Kraus]

Thanks a lot for this answer.


Am 07.05.2018 um 16:06 schrieb David G. Johnston 
mailto:david.g.johns...@gmail.com>>:

On Mon, May 7, 2018 at 6:52 AM, Philipp Kraus 
mailto:philipp.kr...@tu-clausthal.de>> wrote:
Hello,

I have got a complex query with a dynamic column result e.g.:

select builddata('_foo‘);
select * from _foo;

The first is a plsql function which creates a temporary table, but the function 
returns void.
The second call returns all the data from this table. But the columns of the 
temporary table are
not strict fixed, so I cannot return a table by the function.
So my question is, how can I build with this two lines a view, so that I can 
run "select * from myFooView“ or
a function with a dynamic return set of columns e.g. „select myFoo()“?

​Executed queries must have a well-defined column structure at parse/plan-time, 
execution cannot change the columns that are returned.

By extension, a view's column structure must be stable.  Writing:

CREATE VIEW v1 AS
SELECT * FROM tbl1;

Causes the view to defined with all columns of tbl1 as known at the time of the 
view's creation (i.e., * is expanded immediately).

In my case this strict definition is not given, the column number and column 
types are not strict fixed, so based on this a view is not the correct tool.


You might be able to use cursors to accomplish whatever bigger goal you are 
working toward (I'm not particularly fluent with this technique).

​The more direct way to accomplish this is:

SELECT *
FROM func_call() AS (col1 text, col2 int, col3 date)

i.e., have the function return "SETOF record" and then specify the format of 
the returned record when calling the function.

Based on this 
http://www.sqlines.com/postgresql/how-to/return_result_set_from_stored_procedure
but I didn’t find a working solution for my problem at the moment

Phil

Re: Query planner riddle (array-related?)

[Tom Lane]

Markus  writes:
> Ah... yeah, the parallax distribution is fairly sharply peaked around
> 0, so >50 might be severely off.
> So, I've run
>   alter table gaia.dr2light alter parallax set statistics 1000;
>   analyze gaia.dr2light;
> With this, the query plans converge to trivial variations of

>  Hash Join  (cost=253856.23..4775113.84 rows=422 width=1647) (actual 
> time=1967.095..2733.109 rows=18 loops=1)
>Hash Cond: (dr2light.source_id = dr2epochflux.source_id)
>->  Bitmap Heap Scan on dr2light  (cost=24286.88..4385601.28 rows=1297329 
> width=132) (actual time=3.113..19.346 rows=5400 loops=1)
>->  Hash  (cost=118285.38..118285.38 rows=551038 width=1523) (actual 
> time=1885.177..1885.177 rows=550737 loops=1)

> While that's a reasonable plan and fast enough, I'd still like to
> keep the box from seqscanning dr2epochflux with its large arrays and
> use that table's index on source_id.  If I read the query plan right,
> part of the problem is that it still massively overestimates the
> result of parallax>50 (1297329 rather than 5400).  Is there anything
> I can do to improve that estimate?

Raise the parallax stats target still higher, perhaps.  I think we let you
set it as high as 1.

> But even with that suboptimal estimate, postgres, under the
> assumption of something not too far from a uniform distribution on
> source_id, should end up estimating the cardinality of the end result
> as something like
> (selectivity on dr2light)*(cardinality of dr2epochflux),
> and hence roughly (1297329/1.6e9*5e5)=405 rows to be drawn from
> dr2epochflux.  It would seem a lot smarter to just pull these few 1e2
> rows using the source_id index on dr2epochflux than seqscanning that
> table, no?

No.  Given the above estimates, it would have to do 1297329 index lookups
in dr2epochflux, which is not going to be a win compared to 1297329 probes
into an in-memory hash table.  Even with a dead-accurate estimate of 5400
dr2light rows to be joined, I don't think an inner indexscan is
necessarily a better plan than a hash.  It's the number of probes that
matter, not the number of successful probes.

(It's not clear to me why so few of the dr2light rows have join partners,
but the planner does seem to understand that most of them don't.)

You say you're worried about "large arrays" in dr2epochflux; but if they
are large enough to be toasted out-of-line, it's really a nonissue.  Only
the toast pointers would be read during the seqscan or stored in the hash.

regards, tom lane

Renice on Postgresql process

[Ayappan P2]

Hi All,

We are using Postgresql in AIX. Unlike some other databases, Postgresql has
lot of other process running in the background along with the main process.

We do "renice" only on the Postgres main process. Is it sufficient to have
higher priority only for the main process or we have to do "renice" for all
the Postgresql related process ( like wal writer, logger , checkpointer
etc.,) ?

Thanks
Ayappan P

Re: Renice on Postgresql process

[Ben Chobot]

On May 7, 2018, at 7:46 AM, Ayappan P2  wrote:
> 
> Hi All,
> 
> We are using Postgresql in AIX. Unlike some other databases, Postgresql has 
> lot of other process running in the background along with the main process. 
> 
> We do "renice" only on the Postgres main process. Is it sufficient to have 
> higher priority only for the main process or we have to do "renice" for all 
> the Postgresql related process ( like wal writer, logger , checkpointer 
> etc.,) ? 
> 

What do you hope to achieve with your renicing? There is a compelling school of 
thought which holds that nice database processes take longer to relinquish 
their resources, which doesn't end up helping anything at all.

How to manipulate tuples in C functions?

[a]

Hey all:

As far as I know, composite type and rows (trigger functions) are passed 
between PostgreSQL and C functions as tuple. However, I rarely find things 
relating on how to manipulate a tuple under C from documentation. 


The only thing I can find from the doc is very simple stuff like 
'GetAttributeByName' or 'GetAttributeByNum' to retrieve data, or use a 
'BlessTupleDesc' to initialize a 'TupleDesc' variable and 'heap_form_tuple' to 
return a 'HeapTuple' and return a 'HeapTupleGetDatum(HeapTuple tuple)' to 
convert to a 'Datum' type.


However not only the illustration of the previous listed functions are not 
fully described (e.g. return and input of each functions) but also any complex 
situation are stated. What if I have a composite type inside a composite type? 
What if I have a array inside a composite type? What if I have a variable 
length type?


Furthermore, to write a compatible procedures, I would need at least 2 more 
information: 1, The total number of entry inside a tuple; 2, The total space 
the tuple took.


So if anyone can tell me how to answer the above question? Or simply give an 
example of iterator that go through all entries of a tuple? Thank you so much!!

Run external command as part of an sql statement ?

[David Gauthier]

Hi:

At the psql prompt, I can do something like...
   "select  \! id -nu"
...to get the uid of whoever's running psql.

I want to be able to run a shell command like this from within a stored
procedure.  Is there a way to do this ?

Thanks

Re: Run external command as part of an sql statement ?

[David G. Johnston]

On Mon, May 7, 2018 at 2:35 PM, David Gauthier 
wrote:

> Hi:
>
> At the psql prompt, I can do something like...
>"select  \! id -nu"
> ...to get the uid of whoever's running psql.
>
> I want to be able to run a shell command like this from within a stored
> procedure.  Is there a way to do this ?
>
>
​In core, you can probably use the untrusted​ version of Perl, Python, or
Tcl to accomplish your goal.  SQL and pl/pgSQL do not provide that
capability.​

​David J.

Re: Run external command as part of an sql statement ?

[Tom Lane]

"David G. Johnston"  writes:
> On Mon, May 7, 2018 at 2:35 PM, David Gauthier 
> wrote:
>> I want to be able to run a shell command like this from within a stored
>> procedure.  Is there a way to do this ?

> In core, you can probably use the untrusted​ version of Perl, Python, or
> Tcl to accomplish your goal.  SQL and pl/pgSQL do not provide that
> capability.​

Depending on what you want to do, COPY TO/FROM PROGRAM might be a
serviceable option.  But, just like the untrusted-PL variants, you
need to be superuser.  Keep in mind that the program will run as
the database server owner (which is the reason for the superuser
restriction).

regards, tom lane

Re: How to manipulate tuples in C functions?

[Tom Lane]

"=?ISO-8859-1?B?YQ==?=" <372660...@qq.com> writes:
> As far as I know, composite type and rows (trigger functions) are passed 
> between PostgreSQL and C functions as tuple. However, I rarely find things 
> relating on how to manipulate a tuple under C from documentation. 

A lot of low-level stuff like that is only really documented in the server
source code.  You shouldn't hesitate to look around in the server source
and crib from functions that do something related to what you want.

> So if anyone can tell me how to answer the above question? Or simply give an 
> example of iterator that go through all entries of a tuple? Thank you so 
> much!!

I'd suggest record_out() in src/backend/utils/adt/rowtypes.c as a
prototypical example of disassembling an arbitrary tuple passed as
a composite-type argument.  (Note that the environment for trigger
functions is a bit different, mostly for historical reasons.)

regards, tom lane

Re: Run external command as part of an sql statement ?

[Adrian Klaver]

On 05/07/2018 02:35 PM, David Gauthier wrote:

Hi:

At the psql prompt, I can do something like...
    "select  \! id -nu"
...to get the uid of whoever's running psql.
I want to be able to run a shell command like this from within a stored 
procedure.  Is there a way to do this ?


PL/sh?:

https://github.com/petere/plsh

CREATE OR REPLACE FUNCTION id() RETURNS text AS '
#!/bin/sh
id -nu
' LANGUAGE plsh;

aklaver@tito:~> psql -d test -U postgres

test=# select * from id();
id
--
 postgres


It is an untrusted language so you will need to run as a superuser.




Thanks



--
Adrian Klaver
adrian.kla...@aklaver.com

Re: psycopg2.DataError: invalid input syntax for integer: ""

[tango ward]

Hi All,

Thanks for the suggestions.

I managed to fix this by running CASE on the column. I also fix the %s to
avoid SQLi as per discussed in the documentation of psycopg2.

My apologies for consuming your time, it's my first time to work with DB
and DB migration.

Thanks,
J

On Mon, May 7, 2018 at 9:49 PM, Adrian Klaver 
wrote:

> On 05/07/2018 12:28 AM, tango ward wrote:
>
>> I think I've found the culprit of the problem.
>>
>> I have a field which is varchar from the source DB while on the
>> destination DB its integer.
>>
>> Reading the documentation: http://www.postgresqltutorial.
>> com/postgresql-cast/ but it gives me error `psycopg2.DataError: invalid
>> input syntax for integer: ""`
>>
>
> Would need to see your code to be sure, but I am gong to guess you are
> trying to CAST the string to integer in the SQL e.g CAST(some_str_value AS
> INTEGER) or some_str_value::integer. The error you are getting is :
>
> test=# select CAST('' AS INTEGER);
> ERROR:  invalid input syntax for integer: ""
> LINE 1: select CAST('' AS INTEGER);
> ^
> test=# select ''::integer;
> ERROR:  invalid input syntax for integer: ""
> LINE 1: select ''::integer;
>
> Two options:
>
> 1) You will need the catch the '' on the Python side before they get to
> the database and turn them into None(if Nulls allowed in column) or 0
> otherwise.
>
> 2) If possible convert the integer column on the destination db to a
> varchar one. Though I would do some investigation before doing this as this
> may very well mess up other code.
>
>
>> On Mon, May 7, 2018 at 2:39 PM, David G. Johnston <
>> david.g.johns...@gmail.com > wrote:
>>
>> On Sunday, May 6, 2018, tango ward > > wrote:
>>
>> Yes, my apologies.
>>
>> May I also ask if there's a limitation for the number of
>> timestamp with timezone fields in a table?
>>
>>
>> Not one that is likely to matter in practice.  There's a page
>> discussing limitations on the website/docs somewhere if you wish to
>> find out more.
>>
>> David J.
>>
>>
>>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

KeyError: self._index[x]

[tango ward]

Good day,

Apologies for asking again.

I am trying to remove the whitespace on student number by using TRANSLATE()
inside the execute() of psycopg2. Problem that I am getting is, even if I
will just print the row with the column name(e.g. row['snumber']), I am
getting KeyError error message. The code works if i will print the row with
index value(e.g. row[0])

`
cur_p = conn_pr(cursor_factory=psycopg2.extras.DictCursor)
cur_t = conn_t.cursor(cursor_factory=psycopg2.extras.DictCursor)


cur_t.execute("""
SELECT TRANSLATE(snumber, ' ', '')
FROM sprofile """)

for row in cur_t:

`

Re: KeyError: self._index[x]

[tango ward]

Sorry, accidentally pressed send.


cur_p = conn_pr(cursor_factory=psycopg2.extras.DictCursor)
cur_t = conn_t.cursor(cursor_factory=psycopg2.extras.DictCursor)


cur_t.execute("""
SELECT TRANSLATE(snumber, ' ', '')
FROM sprofile """)

# This will result in KeyError
for row in cur_t:
print row['snumber']

# This works fine
for row in cur_t:
print row[0]

Sorry again.

I would really appreciate any suggestions.

Thanks,
J

Re: KeyError: self._index[x]

[David G. Johnston]

On Monday, May 7, 2018, tango ward  wrote:

>
> cur_t.execute("""
> SELECT TRANSLATE(snumber, ' ', '')
> FROM sprofile """)
>
> # This will result in KeyError
> for row in cur_t:
> print row['snumber']
>
> # This works fine
> for row in cur_t:
> print row[0]
>

So apparently when you execute your query the result has at least one
column but that column isn't named "snumber".  I'm sure there is a way in
Python to debug "row" and find out what names it does have.  Or maybe
execute the query in something like psql and observe e column name there.

That said, by default the name of columns whose values are derived by a
single function call should be the name of the function.  So "translate",
not "snumber" - the latter being consumed by the function.  You can as use
"as " to give it a different fixed name and refer to that.

David J.

Re: KeyError: self._index[x]

[Adrian Klaver]

On 05/07/2018 08:50 PM, David G. Johnston wrote:
On Monday, May 7, 2018, tango ward > wrote:



cur_t.execute("""
     SELECT TRANSLATE(snumber, ' ', '')
     FROM sprofile """)

# This will result in KeyError
for row in cur_t:
print row['snumber']

# This works fine
for row in cur_t:
print row[0]


So apparently when you execute your query the result has at least one 
column but that column isn't named "snumber".  I'm sure there is a way 
in Python to debug "row" and find out what names it does have.  Or maybe 


Python 3+
print(row)

Python 2.7
print row


execute the query in something like psql and observe e column name there.

That said, by default the name of columns whose values are derived by a 
single function call should be the name of the function.  So 
"translate", not "snumber" - the latter being consumed by the function.  
You can as use "as " to give it a different fixed name and refer 
to that.


David J.



--
Adrian Klaver
adrian.kla...@aklaver.com

Re: KeyError: self._index[x]

[Adrian Klaver]

On 05/07/2018 08:11 PM, tango ward wrote:

Sorry, accidentally pressed send.


cur_p = conn_pr(cursor_factory=psycopg2.extras.DictCursor)
cur_t = conn_t.cursor(cursor_factory=psycopg2.extras.DictCursor)


cur_t.execute("""
     SELECT TRANSLATE(snumber, ' ', '')
     FROM sprofile """)



DictCursor is a hybrid dict/sequence.


# This will result in KeyError
for row in cur_t:
print row['snumber']


Above you are using it as a dict and as David pointed you would need to 
use translate as the key:


test=> select translate('test', '', '');
 translate
---
 test




# This works fine
for row in cur_t:
print row[0]


Above you are using as a sequence, so the indexing works.



Sorry again.

I would really appreciate any suggestions.

Thanks,
J




--
Adrian Klaver
adrian.kla...@aklaver.com

Re: KeyError: self._index[x]

[tango ward]

Shall I loop using the 'translate' as key to my row?

On Tue, May 8, 2018 at 12:10 PM, Adrian Klaver 
wrote:

> On 05/07/2018 08:11 PM, tango ward wrote:
>
>> Sorry, accidentally pressed send.
>>
>>
>> cur_p = conn_pr(cursor_factory=psycopg2.extras.DictCursor)
>> cur_t = conn_t.cursor(cursor_factory=psycopg2.extras.DictCursor)
>>
>>
>> cur_t.execute("""
>>  SELECT TRANSLATE(snumber, ' ', '')
>>  FROM sprofile """)
>>
>>
> DictCursor is a hybrid dict/sequence.
>
> # This will result in KeyError
>> for row in cur_t:
>> print row['snumber']
>>
>
> Above you are using it as a dict and as David pointed you would need to
> use translate as the key:
>
> test=> select translate('test', '', '');
>  translate
> ---
>  test
>
>
>
>> # This works fine
>> for row in cur_t:
>> print row[0]
>>
>
> Above you are using as a sequence, so the indexing works.
>
>
>
>> Sorry again.
>>
>> I would really appreciate any suggestions.
>>
>> Thanks,
>> J
>>
>>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Re: KeyError: self._index[x]

[tango ward]

Yeah you're right, making 'translate' as the key works. Thanks for pointing
this out Sir David "That said, by default the name of columns whose values
are derived by a single function call should be the name of the function."
I didn't know it. Is it only in psycopg2 that the name of the columns will
use the name of the function?

On Tue, May 8, 2018 at 12:40 PM, tango ward  wrote:

> Shall I loop using the 'translate' as key to my row?
>
> On Tue, May 8, 2018 at 12:10 PM, Adrian Klaver 
> wrote:
>
>> On 05/07/2018 08:11 PM, tango ward wrote:
>>
>>> Sorry, accidentally pressed send.
>>>
>>>
>>> cur_p = conn_pr(cursor_factory=psycopg2.extras.DictCursor)
>>> cur_t = conn_t.cursor(cursor_factory=psycopg2.extras.DictCursor)
>>>
>>>
>>> cur_t.execute("""
>>>  SELECT TRANSLATE(snumber, ' ', '')
>>>  FROM sprofile """)
>>>
>>>
>> DictCursor is a hybrid dict/sequence.
>>
>> # This will result in KeyError
>>> for row in cur_t:
>>> print row['snumber']
>>>
>>
>> Above you are using it as a dict and as David pointed you would need to
>> use translate as the key:
>>
>> test=> select translate('test', '', '');
>>  translate
>> ---
>>  test
>>
>>
>>
>>> # This works fine
>>> for row in cur_t:
>>> print row[0]
>>>
>>
>> Above you are using as a sequence, so the indexing works.
>>
>>
>>
>>> Sorry again.
>>>
>>> I would really appreciate any suggestions.
>>>
>>> Thanks,
>>> J
>>>
>>>
>>
>> --
>> Adrian Klaver
>> adrian.kla...@aklaver.com
>>
>
>

Re: KeyError: self._index[x]

[David G. Johnston]

On Monday, May 7, 2018, tango ward  wrote:

>  I didn't know it. Is it only in psycopg2 that the name of the columns
> will use the name of the function?
>

The server assigns column names - hence the advice to use psql to
investigate SQL issues more easily since there is one less moving part to
deal with.

David J.

Re: KeyError: self._index[x]

[tango ward]

Thanks, now I understand. Thank you so much for being so helpful to a
newbie same with Sir Adrian.

On Tue, May 8, 2018 at 12:58 PM, David G. Johnston <
david.g.johns...@gmail.com> wrote:

> On Monday, May 7, 2018, tango ward  wrote:
>
>>  I didn't know it. Is it only in psycopg2 that the name of the columns
>> will use the name of the function?
>>
>
> The server assigns column names - hence the advice to use psql to
> investigate SQL issues more easily since there is one less moving part to
> deal with.
>
> David J.
>
>

Re: Renice on Postgresql process

[Ayappan P2]

We are doing "renice" on the main Postgresql process to give higher scheduling priority because other critical operations depends on the database.
You are saying that the database processes take longer to relinquish their resources and we won't achieve anything out of renice, So i assume renice of the database processes is not at all required ?
 
Thanks
Ayappan P
 
- Original message -From: Ben Chobot To: Ayappan P2 Cc: pgsql-general@lists.postgresql.orgSubject: Re: Renice on Postgresql processDate: Mon, May 7, 2018 11:35 PM On May 7, 2018, at 7:46 AM, Ayappan P2  wrote:

 
Hi All,We are using Postgresql in AIX. Unlike some other databases, Postgresql has lot of other process running in the background along with the main process. We do "renice" only on the Postgres main process. Is it sufficient to have higher priority only for the main process or we have to do "renice" for all the Postgresql related process ( like wal writer, logger , checkpointer etc.,) ?  

What do you hope to achieve with your renicing? There is a compelling school of thought which holds that nice database processes take longer to relinquish their resources, which doesn't end up helping anything at all.