Re: Odd behavior with 'currval'

On Thu, Feb 8, 2018 at 9:09 AM, Steven Hirsch wrote: > I have a body of code using JDBC to work with a PostgreSQL 9.6 database. > All tables use 'SERIAL' or 'BIGSERIAL' types to generate ids. All are > working correctly in terms of using the next value as a default. However,

Re: Odd behavior with 'currval'

On 02/08/2018 09:58 AM, Steven Hirsch wrote: On Thu, 8 Feb 2018, Francisco Olarte wrote: Something must be different. As requested by others, try posting the SQL code chunks, more eyeballs make bugs shallower ( it's happened several times to me, make a typo, go over it for half an hour, grab a

Re: Odd behavior with 'currval'

On Thu, 8 Feb 2018, Melvin Davidson wrote: I believe your problem is in your usage. In order for currval(regclass) to work, you must first do a SELECT nextval(regclass) in your _current transaction_! https://www.postgresql.org/docs/9.6/static/functions-sequence.html I AM doing that. It is

Re: there is a great difference between the query execution time and the log record time

On 02/08/2018 08:35 AM, dby...@163.com wrote: Hi,everybody recently, I found some slow SQL runtime as long as 4 seconds in postgres log. However, when I was executing on PSQL client, it takes only 3.6 milliseconds, I did the following things. 1. add auto_explain session_preload_libraries =

Re: Odd behavior with 'currval'

On 02/08/2018 10:20 AM, Steven Hirsch wrote: On Thu, 8 Feb 2018, Adrian Klaver wrote: What if you do?: SELECT * FROM udm_asset_type_definition_def_id_seq; I get: udm_asset_type_definition_def_id_seq    21    1    1 9223372036854775807    1    1    32    false    true SELECT

Re: PITR Multiple recoveries

On Thu, Feb 8, 2018 at 5:07 AM, Sébastien Boutté wrote: > Hi all, > > I'm trying to make my server doing PITR backup, i follow the rules on > https://www.postgresql.org/docs/9.5/static/continuous-archiving.html. > > On my local server, i would like to resync multiple

回复：there is a great difference between the query execution time and the log record time

i test psql on local and remote server are the same！ the jdbc remote client is display data on web page！ | | 邓彪 邮箱：dby...@163.com | 签名由 网易邮箱大师 定制 在2018年02月09日 01:33，Adrian Klaver 写道： On 02/08/2018 09:14 AM, 邓彪 wrote: > the client on the remote server！！！ So when you did the psql test was

Re: there is a great difference between the query execution time and the log record time

the client on the remote server！！！ | | 邓彪 邮箱：dby...@163.com | 签名由 网易邮箱大师 定制 On 02/09/2018 00:54, Adrian Klaver wrote: On 02/08/2018 08:51 AM, nemo wrote: > Hi,the client is jdbc. Is the client on the server or is it remote? I am not a Java programmer, but for those that are and might be

Re: Odd behavior with 'currval'

Steven: On Thu, Feb 8, 2018 at 6:58 PM, Steven Hirsch wrote: > On Thu, 8 Feb 2018, Francisco Olarte wrote: >> Something must be different. As requested by others, try posting the >> SQL code chunks, more eyeballs make bugs shallower ( it's happened >> several times to me,

Re: Odd behavior with 'currval'

On 02/08/2018 08:09 AM, Steven Hirsch wrote: I have a body of code using JDBC to work with a PostgreSQL 9.6 database. All tables use 'SERIAL' or 'BIGSERIAL' types to generate ids.  All are working correctly in terms of using the next value as a default. However, reading back the most recently

Re: Odd behavior with 'currval'

On Thu, Feb 8, 2018 at 1:27 PM, Adrian Klaver wrote: > On 02/08/2018 10:20 AM, Steven Hirsch wrote: > >> On Thu, 8 Feb 2018, Adrian Klaver wrote: >> >> What if you do?: >>> >>> SELECT * FROM udm_asset_type_definition_def_id_seq; >>> >> >> I get: >> >>

Re: Odd behavior with 'currval'

On 02/08/2018 10:54 AM, Steven Hirsch wrote: On Thu, 8 Feb 2018, Adrian Klaver wrote: SELECT currval('udm_asset_type_definition_id_seq'); Arrgh my mistake, the above should have been SELECT currval('udm_asset_type_definition_def_id_seq'); [Code: , SQL State: 55000]  ERROR: currval of

Re: Odd behavior with 'currval'

On Thu, Feb 8, 2018 at 5:09 PM, Steven Hirsch wrote: > I have a body of code using JDBC to work with a PostgreSQL 9.6 database. All .. > The code being used in the failing case is not the slightest bit different > from the working cases in terms of structure and

Re: Odd behavior with 'currval'

On Thu, Feb 8, 2018 at 1:17 PM, Melvin Davidson wrote: > > > On Thu, Feb 8, 2018 at 1:09 PM, Adrian Klaver > wrote: > >> On 02/08/2018 09:58 AM, Steven Hirsch wrote: >> >>> On Thu, 8 Feb 2018, Francisco Olarte wrote: >>> >>> Something must be

Re: Odd behavior with 'currval'

On 02/08/2018 10:20 AM, Steven Hirsch wrote: On Thu, 8 Feb 2018, Adrian Klaver wrote: What if you do?: SELECT * FROM udm_asset_type_definition_def_id_seq; I get: udm_asset_type_definition_def_id_seq    21    1    1 9223372036854775807    1    1    32    false    true What does the

Re: Odd behavior with 'currval'

On Thu, 8 Feb 2018, Adrian Klaver wrote: hplc=> \d student_attendance_attendance_id_seq Sequence "public.student_attendance_attendance_id_seq" Column | Type |Value ---+-+-- sequence_name | name|

Re: Odd behavior with 'currval'

On 02/08/2018 11:12 AM, Steven Hirsch wrote: On Thu, 8 Feb 2018, Adrian Klaver wrote: hplc=> \d student_attendance_attendance_id_seq     Sequence "public.student_attendance_attendance_id_seq"    Column |  Type   |    Value

Re: Odd behavior with 'currval'

On Thu, Feb 8, 2018 at 10:58 AM, Steven Hirsch wrote: > On a hunch, I tried 'SELECT currval(NULL)' to see if it returned '0', but > that too returns NULL. So, where is the '0' coming from when I do: > > SELECT currval( pg_get_serial_sequence('udm_as >

Re: Odd behavior with 'currval'

On Thu, Feb 8, 2018 at 12:54 PM, David G. Johnston < david.g.johns...@gmail.com> wrote: > The only 'currval' procedure is the one defined at installation (in >> public). >> > ​So, the installed version of currval would be defined in "pg_catalog", not "public" ... David J. ​

Re: Odd behavior with 'currval'

On Thu, Feb 8, 2018 at 12:51 PM, Steven Hirsch wrote: > On Thu, 8 Feb 2018, David G. Johnston wrote: > > On Thu, Feb 8, 2018 at 10:58 AM, Steven Hirsch wrote: >> On a hunch, I tried 'SELECT currval(NULL)' to see if it returned >> '0', but that too

Re: PITR Multiple recoveries

Hi Jeff, You mean that ? Is this correct ? hot_stanby = on to be sure it plays WAL and stays in standby mode ? 1. Stop Postgres 2. Restore Local basebackup (untar, un gzip) 3. Create (recovery.conf) standby_mode + hot_standby on (postgresql.conf) 4. Start Postgres 5. Wait to see in log

Re: Odd behavior with 'currval'

On Thu, 8 Feb 2018, David G. Johnston wrote: On Thu, Feb 8, 2018 at 10:58 AM, Steven Hirsch wrote: On a hunch, I tried 'SELECT currval(NULL)' to see if it returned '0', but that too returns NULL.  So, where is the '0' coming from when I do: SELECT

Re: DOW is 0-based?

Hi, On Thu, Feb 8, 2018 at 2:24 PM, Igal @ Lucee.org wrote: > Is there a rational reason why Day of the Week is 0-based, i.e. Sunday (0) > to Saturday (6) instead of the more intuitive Sunday (1) to Saturday (7)? > > SELECT date_part('dow', current_date); > >

Re: Odd behavior with 'currval'

On Thu, 8 Feb 2018, David G. Johnston wrote: On Thu, Feb 8, 2018 at 12:54 PM, David G. Johnston wrote:  The only 'currval' procedure is the one defined at installation (in public). ​So, the installed version of currval would be defined in

Re: Odd behavior with 'currval'

On Thu, Feb 8, 2018 at 2:22 PM, Steven Hirsch wrote: > On Thu, 8 Feb 2018, David G. Johnston wrote: > > On Thu, Feb 8, 2018 at 12:54 PM, David G. Johnston < >> david.g.johns...@gmail.com> wrote: >> The only 'currval' procedure is the one defined at >>

List all columns referencing an FK

Hi all.   Back in 2008 I asked this question:  http://www.postgresql-archive.org/Finding-all-tables-that-have-foreign-keys-referencing-a-table-td2153236.html   The solution was (and still is) this: select confrelid::regclass, af.attname as fcol, conrelid::regclass, a.attname ascol from

Odd behavior with 'currval'

I have a body of code using JDBC to work with a PostgreSQL 9.6 database. All tables use 'SERIAL' or 'BIGSERIAL' types to generate ids. All are working correctly in terms of using the next value as a default. However, reading back the most recently applied (currval) value is failing for one

there is a great difference between the query execution time and the log record time

Hi,everybody recently, I found some slow SQL runtime as long as 4 seconds in postgres log. However, when I was executing on PSQL client, it takes only 3.6 milliseconds, I did the following things. 1. add auto_explain session_preload_libraries = 'auto_explain' auto_explain.log_min_duration = 100

Re: there is a great difference between the query execution time and the log record time

On Thu, Feb 8, 2018 at 5:35 PM, dby...@163.com wrote: > Hi,everybody recently, I found some slow SQL runtime as long as 4 seconds in > postgres log. However, when I was executing on PSQL client, it takes only > 3.6 milliseconds, I did the following things. You are not doing

Re: "could not receive data from client" && "incomplete startup packet"

Hi Alvaro: It's a corporate "public" posting, so I can't simply install stuff there. However, I can request that new version(s) be put there. What version would you recommend ? -dave On Wed, Feb 7, 2018 at 6:24 PM, Alvaro Herrera wrote: > David Gauthier wrote: > > I

PITR Multiple recoveries

Hi all, I'm trying to make my server doing PITR backup, i follow the rules on https://www.postgresql.org/docs/9.5/static/continuous-archiving.html. On my local server, i would like to resync multiple times my local database: What i'm doing : 1. Stop Postgres 2. Restore Local basebackup (untar,

Re: "could not receive data from client" && "incomplete startup packet"

David Gauthier writes: > It's a corporate "public" posting, so I can't simply install stuff there. > However, I can request that new version(s) be put there. What version > would you recommend ? The important point is that you should be running the latest, or nearly