Re: Renice on Postgresql process

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

Re: KeyError: self._index[x]

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 t

Re: KeyError: self._index[x]

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 de

Re: KeyError: self._index[x]

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 wil

Re: KeyError: self._index[x]

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_factor

Re: KeyError: self._index[x]

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 s

Re: KeyError: self._index[x]

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

Re: KeyError: self._index[x]

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 ap

Re: KeyError: self._index[x]

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 f

KeyError: self._index[x]

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 c

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

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 a

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

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:

Re: How to manipulate tuples in C functions?

"=?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

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

"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 accompli

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

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 thi

Run external command as part of an sql statement ?

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

How to manipulate tuples in C functions?

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 'Get

Re: Renice on Postgresql process

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 hav

Renice on Postgresql process

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 "

Re: Query planner riddle (array-related?)

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 > H

Re: void function and view select

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 builddat

Re: void function and view select

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

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

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) V

void function and view select

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 temporar

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

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.

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

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 fe

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

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

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

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 m

Re: Add schema to the query

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 = ? >>

Re: postgres on physical replica crashes

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 phy

Re: Query planner riddle (array-related?)

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.dr

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

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 ther

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

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 a

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

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 t

Is it possible to get username information while writing trigger?

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

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

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: ""`