List all columns referencing an FK

[Andreas Joseph Krogh]

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 pg_attribute af, pg_attribute a, (select conrelid,confrelid,conkey[i]
asconkey, confkey[i] as confkey from (select conrelid,confrelid,conkey,confkey, 
generate_series(1,array_upper(conkey,1)) as i from pg_constraint where contype =
'f') ss) ss2 where af.attnum = confkey and af.attrelid = confrelid and a.attnum 
= conkeyand a.attrelid = conrelid AND confrelid::regclass = 'onp_user'::
regclass ANDaf.attname = 'id' 
 
(it lists all columns in all tables referencing the onp_user.id column)
 
I wonder, is this now possible using information_schema only, or are there 
still pieces missing in the standard holding this back?
 
-- Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
andr...@visena.com 
www.visena.com 
 

PITR Multiple recoveries

[Sébastien Boutté]

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, un gzip)
3. Create recovery.conf
4. Start Postgres
5. Wait recovery.done
6. At this point, it's OK.
7. Stop Postgres
8. . some time passed
9. resync wals with latest
10. recreate recovery.conf
11. Start Postgres
11. Wait recovery.done
12. At this point, it's NOK. Latest wals have not been played on local
server.

Do you know what i'm missing ? Thanks

Regards,

Sebastien Boutte

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

[David Gauthier]

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 thin kit's 9.3.2 :-(
> >
> > % psql -V
> > psql (PostgreSQL) 9.3.2
>
> Ah, you're screwed then.  My condolences.
>
> > These are my options...
> > ls -ld /tool/pandora64/.package/postgresql*
> > drwxr-xr-x 8 pandora pandora 4096 Sep  2  2016 /tool/pandora64/.package/
> postgresql-8.2.3
> > drwxr-xr-x 8 pandora pandora 4096 Sep 16  2013 /tool/pandora64/.package/
> postgresql-8.3.0
> > drwxr-xr-x 6 pandora pandora 4096 Aug 31  2015 /tool/pandora64/.package/
> postgresql-9.0.2
> > drwxr-xr-x 6 pandora pandora 4096 Oct 11  2013 /tool/pandora64/.package/
> postgresql-9.3.0
> > drwxr-xr-x 6 pandora pandora 4096 May 30  2014 /tool/pandora64/.package/
> postgresql-9.3.2
> > drwxr-xr-x 6 pandora pandora 4096 Oct 19  2016 /tool/pandora64/.package/
> postgresql-9.6.0
> >
> > What about 9.6.0 ?
>
> These are all terrible choices.  I suggest you change to some other
> packaging tool, if this pandora64 thing (whatever it is) only offers
> these particular Postgres versions.
>
> --
> Álvaro Herrerahttps://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>

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

[Tom Lane]

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
latest, minor release in whichever PG release series you've chosen.
The reason this list seems like such a fail:

>>> These are my options...
>>> ls -ld /tool/pandora64/.package/postgresql*
>>> drwxr-xr-x 8 pandora pandora 4096 Sep  2  2016 
>>> /tool/pandora64/.package/postgresql-8.2.3
>>> drwxr-xr-x 8 pandora pandora 4096 Sep 16  2013 
>>> /tool/pandora64/.package/postgresql-8.3.0
>>> drwxr-xr-x 6 pandora pandora 4096 Aug 31  2015 
>>> /tool/pandora64/.package/postgresql-9.0.2
>>> drwxr-xr-x 6 pandora pandora 4096 Oct 11  2013 
>>> /tool/pandora64/.package/postgresql-9.3.0
>>> drwxr-xr-x 6 pandora pandora 4096 May 30  2014 
>>> /tool/pandora64/.package/postgresql-9.3.2
>>> drwxr-xr-x 6 pandora pandora 4096 Oct 19  2016 
>>> /tool/pandora64/.package/postgresql-9.6.0

is that whoever is producing the packages seems to be content to wrap one
early release in each branch (or some branches) and then never follow up.
Those early releases are, almost by definition, not as stable as their
branches will be in a year or two.  They also contain known security
issues of assorted severities.

For reference, the current latest minor releases are 10.2, 9.6.7, 9.5.11,
9.4.16, 9.3.21 (all newly minted today, as it happens).  The 9.0.x and
8.x.x release branches have been out of support for years.

regards, tom lane

Odd behavior with 'currval'

[Steven Hirsch]

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 table, where it always returns '0'. Note that the table data shows 
the expected value when queried by SELECT!  It is only the currval() 
function that is wrong.  I am properly guarding for SQL exceptions and 
none are being thrown.


The code being used in the failing case is not the slightest bit different 
from the working cases in terms of structure and transaction control - 
only the SQL, column count, etc. is different (but correctly formed and in 
all other ways functional).


I'm not sure where to start debugging this.  Can anyone give me even a 
working theory to explain how returning a bogus value is possible?  When I 
look at the sequences in pgAdmin, they are as expected in terms of 
ownership, etc.  And, again, the table IS getting the correct value.


Thanks much for any ideas!

--

Re: PITR Multiple recoveries

[Jeff Janes]

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 times my local
> database:
>
> What i'm doing :
> 1. Stop Postgres
> 2. Restore Local basebackup (untar, un gzip)
> 3. Create recovery.conf
> 4. Start Postgres
> 5. Wait recovery.done
> 6. At this point, it's OK.
>

Once recovery is done and the database is open, it is now a primary.  It is
no longer eligible for further WAL replay.
(Unless it is was only open in standby mode.)

Cheers,

Jeff

Re: Odd behavior with 'currval'

[David G. Johnston]

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,
> reading back the most recently applied (currval) value is failing for one
> table, where it always returns '0'. Note that the table data shows the
> expected value when queried by SELECT!  It is only the currval() function
> that is wrong.  I am properly guarding for SQL exceptions and none are
> being thrown.
>
> The code being used in the failing case is not the slightest bit different
> from the working cases in terms of structure and transaction control - only
> the SQL, column count, etc. is different (but correctly formed and in all
> other ways functional).
>
> I'm not sure where to start debugging this.  Can anyone give me even a
> working theory to explain how returning a bogus value is possible?  When I
> look at the sequences in pgAdmin, they are as expected in terms of
> ownership, etc.  And, again, the table IS getting the correct value.
>

Its seems that whatever name you are passing into currval must match an
existing sequence ​but it doesn't match the sequence name that is attached
to the table.col default expression in question.

search_path considerations might play a role if its not a simple copy-paste
type error...but its hard to blindly suggest potential reasons.

David J.

Re: Odd behavior with 'currval'

[Adrian Klaver]

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 applied (currval) value is 
failing for one table, where it always returns '0'. Note that the table 
data shows the expected value when queried by SELECT!  It is only the 
currval() function that is wrong.  I am properly guarding for SQL 
exceptions and none are being thrown.


The code being used in the failing case is not the slightest bit 
different from the working cases in terms of structure and transaction 
control - only the SQL, column count, etc. is different (but correctly 
formed and in all other ways functional).


Can we see the table schema and the currval code?

Is there anything in the Postgres logs that might help?



I'm not sure where to start debugging this.  Can anyone give me even a 
working theory to explain how returning a bogus value is possible?  When 
I look at the sequences in pgAdmin, they are as expected in terms of 
ownership, etc.  And, again, the table IS getting the correct value.


Thanks much for any ideas!




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

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

[dby...@163.com]

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
auto_explain.log_analyze = true
auto_explain.log_buffers = true
​
2. set lock debug
log_lock_waits = on 
deadlock_timeout = 500ms
log_min_duration_statement = 100
Then i find it takes only 2 milliseconds(auto_explain),but in postgres log it 
takes 4 seconds, please help me find this problem!!!
env information
$ uname -a
Linux 3.10.0-693.el7.x86_64 #1 SMP Wed Aug 2 06:49:08 PDT 2017 x86_64 x86_64 
x86_64 GNU/Linux
​
postgres=> select version();
  version   
   
---
 PostgreSQL 9.6.2, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-18), 
64-bit
​
slow sql log

2018-02-06 14:52:52.097 
CST,"postgres","database",63747,"KFCS2-14:58241",5a794e91.f903,3,"SELECT",2018-02-06
 14:43:29 CST,5/554,0,LOG,0,"duration: 4347.635 ms  execute : 
select orderprodu0_.order_id as order_id1_19_, orderprodu0_.amount1 as 
amount2_19_, orderprodu0_.amount2 as amount3_19_, orderprodu0_.amount3 as 
amount4_19_, orderprodu0_.amount4 as amount5_19_, orderprodu0_.amount5 as 
amount6_19_, orderprodu0_.auto_confirm_time as auto_con7_19_, 
orderprodu0_.bestowed_sum_score as bestowed8_19_, orderprodu0_.board_train_code 
as board_tr9_19_, orderprodu0_.bureau_code as bureau_10_19_, 
orderprodu0_.coach_no as coach_n11_19_, orderprodu0_.comments_info1 as 
comment12_19_, orderprodu0_.comments_info2 as comment13_19_, 
orderprodu0_.company_abbreviation as company14_19_, orderprodu0_.company_id as 
company15_19_, orderprodu0_.complain_flag as complai16_19_, 
orderprodu0_.day_night as day_nig17_19_, orderprodu0_.deliver_mode as 
deliver18_19_, orderprodu0_.destination_code as destina19_19_, 
orderprodu0_.destination_name as destina20_19_, orderprodu0_.destination_type 
as destina21_19_, orderprodu0_.drawee_name as drawee_22_19_, 
orderprodu0_.evaluate_flag as evaluat23_19_, orderprodu0_.exception_flag as 
excepti24_19_, orderprodu0_.flag1 as flag25_19_, orderprodu0_.flag2 as 
flag26_19_, orderprodu0_.flag3 as flag27_19_, orderprodu0_.flag4 as flag28_19_, 
orderprodu0_.flag5 as flag29_19_, orderprodu0_.freight as freight30_19_, 
orderprodu0_.from_station_name as from_st31_19_, orderprodu0_.from_tele_code as 
from_te32_19_, orderprodu0_.group_no as group_n33_19_, 
orderprodu0_.invoice_email as invoice34_19_, orderprodu0_.invoice_flag as 
invoice35_19_, orderprodu0_.invoice_state as invoice36_19_, 
orderprodu0_.last_modify_time as last_mo37_19_, orderprodu0_.made_flag as 
made_fl38_19_, orderprodu0_.mobile as mobile39_19_, orderprodu0_.natural_day as 
natural40_19_, orderprodu0_.office_no as office_41_19_, 
orderprodu0_.operator_id as operato42_19_, orderprodu0_.order_batch as 
order_b43_19_, orderprodu0_.order_code as order_c44_19_, 
orderprodu0_.order_date as order_d45_19_, orderprodu0_.order_day as 
order_d46_19_, orderprodu0_.order_pay_method as order_p47_19_, 
orderprodu0_.order_pay_type as order_p48_19_, orderprodu0_.order_state as 
order_s49_19_, orderprodu0_.order_total_price as order_t50_19_, 
orderprodu0_.orders_out_time as orders_51_19_, orderprodu0_.passenger_name as 
passeng52_19_, orderprodu0_.passenger_phone as passeng53_19_, 
orderprodu0_.pay_out_time as pay_out54_19_, orderprodu0_.payment_info_id as 
payment55_19_, orderprodu0_.print_time as print_t56_19_, 
orderprodu0_.product_total_price as product57_19_, orderprodu0_.return_reason 
as return_58_19_, orderprodu0_.sales_channels as sales_c59_19_, 
orderprodu0_.seat_no as seat_no60_19_, orderprodu0_.seat_type_code as 
seat_ty61_19_, orderprodu0_.sell_station as sell_st62_19_, 
orderprodu0_.sequence_no as sequenc63_19_, orderprodu0_.service_date as 
service64_19_, orderprodu0_.service_time as service65_19_, 
orderprodu0_.start_time as start_t66_19_, orderprodu0_.start_train_date as 
start_t67_19_, orderprodu0_.start_train_time as start_t68_19_, 
orderprodu0_.statistics_date as statist69_19_, orderprodu0_.synchronous_flag as 
synchro70_19_, orderprodu0_.synchronous_time as synchro71_19_, 
orderprodu0_.ticket_no as ticket_72_19_, orderprodu0_.to_station_name as 
to_stat73_19_, orderprodu0_.to_tele_code as to_tele74_19_, 
orderprodu0_.train_code as train_c75_19_, orderprodu0_.train_date as 
train_d76_19_, orderprodu0_.train_no as train_n77_19_, 
orderprodu0_.train_setname as train_s78_19_, orderprodu0_.transmit_flag as 
transmi79_19_, orderprodu0_.transmit_time as transmi80_19_, 
orderprodu0_.user_id as user_id81_19_, orderprodu0_.user_name as user_na82_19_, 
orderprodu0_.valid_flag as valid_f83_19_, orderprodu0_.window_no as 
window_84_19_ from tablename orderprodu0_ where orderprodu0_.company_id=$1 and 
or

Re: Odd behavior with 'currval'

[Francisco Olarte]

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 transaction control - only
> the SQL, column count, etc. is different (but correctly formed and in all
> other ways functional).

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
colleague, she immediately points to it )

> I'm not sure where to start debugging this.  Can anyone give me even a
> working theory to explain how returning a bogus value is possible?  When I
> look at the sequences in pgAdmin, they are as expected in terms of
> ownership, etc.  And, again, the table IS getting the correct value.

It shouldn't be, this I why several perople are requesting to see the
relevant code. Experience says lots of this fails are pilot error.

As an aside, with recent postgres versions you can normally use the
returning construct to grab autogenerated id. I.e., instead of "insert
blah-blah-blah, select currval(), whatever else" you can many times do
"insert balh-blah-blah returning auto_gen_column, whatever else". I've
used it a lot, and normally leads to shorter/easier/faster code.

Francisco Olarte.

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

[Adrian Klaver]

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 = 'auto_explain'
auto_explain.log_min_duration = 100
auto_explain.log_analyze = true
auto_explain.log_buffers = true
​
2. set lock debug
log_lock_waits = on
deadlock_timeout = 500ms
log_min_duration_statement = 100


Then i find it takes only 2 milliseconds(auto_explain),but in
postgres log it takes 4 seconds, please help me find this problem!!!


What client is running the SQL that shows up in the log?




env information

$ uname -a
Linux 3.10.0-693.el7.x86_64 #1 SMP Wed Aug 2 06:49:08 PDT 2017 x86_64 
x86_64 x86_64 GNU/Linux

​
postgres=> select version();
                                             version
---
PostgreSQL 9.6.2, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 
4.4.7-18), 64-bit

​


slow sql log


2018-02-06 14:52:52.097 
CST,"postgres","database",63747,"KFCS2-14:58241",5a794e91.f903,3,"SELECT",2018-02-06 
14:43:29 CST,5/554,0,LOG,0,"duration: 4347.635 ms execute 
: select orderprodu0_.order_id as order_id1_19_, 
orderprodu0_.amount1 as amount2_19_, orderprodu0_.amount2 as 
amount3_19_, orderprodu0_.amount3 as amount4_19_, orderprodu0_.amount4 
as amount5_19_, orderprodu0_.amount5 as amount6_19_, 
orderprodu0_.auto_confirm_time as auto_con7_19_, 
orderprodu0_.bestowed_sum_score as bestowed8_19_, 
orderprodu0_.board_train_code as board_tr9_19_, orderprodu0_.bureau_code 
as bureau_10_19_, orderprodu0_.coach_no as coach_n11_19_, 
orderprodu0_.comments_info1 as comment12_19_, 
orderprodu0_.comments_info2 as comment13_19_, 
orderprodu0_.company_abbreviation as company14_19_, 
orderprodu0_.company_id as company15_19_, orderprodu0_.complain_flag as 
complai16_19_, orderprodu0_.day_night as day_nig17_19_, 
orderprodu0_.deliver_mode as deliver18_19_, 
orderprodu0_.destination_code as destina19_19_, 
orderprodu0_.destination_name as destina20_19_, 
orderprodu0_.destination_type as destina21_19_, orderprodu0_.drawee_name 
as drawee_22_19_, orderprodu0_.evaluate_flag as evaluat23_19_, 
orderprodu0_.exception_flag as excepti24_19_, orderprodu0_.flag1 as 
flag25_19_, orderprodu0_.flag2 as flag26_19_, orderprodu0_.flag3 as 
flag27_19_, orderprodu0_.flag4 as flag28_19_, orderprodu0_.flag5 as 
flag29_19_, orderprodu0_.freight as freight30_19_, 
orderprodu0_.from_station_name as from_st31_19_, 
orderprodu0_.from_tele_code as from_te32_19_, orderprodu0_.group_no as 
group_n33_19_, orderprodu0_.invoice_email as invoice34_19_, 
orderprodu0_.invoice_flag as invoice35_19_, orderprodu0_.invoice_state 
as invoice36_19_, orderprodu0_.last_modify_time as last_mo37_19_, 
orderprodu0_.made_flag as made_fl38_19_, orderprodu0_.mobile as 
mobile39_19_, orderprodu0_.natural_day as natural40_19_, 
orderprodu0_.office_no as office_41_19_, orderprodu0_.operator_id as 
operato42_19_, orderprodu0_.order_batch as order_b43_19_, 
orderprodu0_.order_code as order_c44_19_, orderprodu0_.order_date as 
order_d45_19_, orderprodu0_.order_day as order_d46_19_, 
orderprodu0_.order_pay_method as order_p47_19_, 
orderprodu0_.order_pay_type as order_p48_19_, orderprodu0_.order_state 
as order_s49_19_, orderprodu0_.order_total_price as order_t50_19_, 
orderprodu0_.orders_out_time as orders_51_19_, 
orderprodu0_.passenger_name as passeng52_19_, 
orderprodu0_.passenger_phone as passeng53_19_, orderprodu0_.pay_out_time 
as pay_out54_19_, orderprodu0_.payment_info_id as payment55_19_, 
orderprodu0_.print_time as print_t56_19_, 
orderprodu0_.product_total_price as product57_19_, 
orderprodu0_.return_reason as return_58_19_, orderprodu0_.sales_channels 
as sales_c59_19_, orderprodu0_.seat_no as seat_no60_19_, 
orderprodu0_.seat_type_code as seat_ty61_19_, orderprodu0_.sell_station 
as sell_st62_19_, orderprodu0_.sequence_no as sequenc63_19_, 
orderprodu0_.service_date as service64_19_, orderprodu0_.service_time as 
service65_19_, orderprodu0_.start_time as start_t66_19_, 
orderprodu0_.start_train_date as start_t67_19_, 
orderprodu0_.start_train_time as start_t68_19_, 
orderprodu0_.statistics_date as statist69_19_, 
orderprodu0_.synchronous_flag as synchro70_19_, 
orderprodu0_.synchronous_time as synchro71_19_, orderprodu0_.ticket_no 
as ticket_72_19_, orderprodu0_.to_station_name as to_stat73_19_, 
orderprodu0_.to_tele_code as to_tele74_19_, orderprodu0_.train_code as 
train_c75_19_, orderprodu0_.train_date as train_d76_19_, 
orderprodu0_.train_no as train_n77_19_, orderprodu0_.train_setname as 
train_s78_19_, orderprodu0_.transmit_flag as transmi79_19_, 
orderprodu0_.transmit_time as transmi80_19_, orderprodu0_.user_id as 
user_id81_19_, orderprodu0_.user_name as user_na82_19_, 
orderprodu0_.valid_flag as valid_f83_19_, orderprodu0_.wind

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

[Francisco Olarte]

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 the same thing ( explain discards all data in the
server ) and your query has a lot of columns. I'm not sure if it does
affect, but are you sure you are not gettin more time for transmitting
the actual result data?

Francisco Olarte.

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

[nemo]

Hi,from the explain the sql result is only 338 rows!



--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

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

[nemo]

Hi,the client is jdbc.



--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

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

[Adrian Klaver]

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 able to 
help could you provide the code that runs the query?






--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html




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

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 able to
help could you provide the code that runs the query?

>
>
>
> --
> Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html
>


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

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

[Adrian Klaver]

On 02/08/2018 09:14 AM, 邓彪 wrote:

the client on the remote server！！！


So when you did the psql test was that local to the server or remote?

Also what is the JDBC remote client doing with the data?
For example is is feeding a GUI form?





邓彪
邮箱：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 able to
help could you provide the code that runs the query?

 >
 >
 >
 > --
 > Sent from:
http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html
 >


-- 
Adrian Klaver

adrian.kla...@aklaver.com




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

回复：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 that local to the server or remote?

Also what is the JDBC remote client doing with the data?
For example is is feeding a GUI form?

>
>
>
> 邓彪
> 邮箱：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 able to
> help could you provide the code that runs the query?
>
>  >
>  >
>  >
>  > --
>  > Sent from:
> http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html
>  >
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>


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

Re: Odd behavior with 'currval'

[Steven Hirsch]

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
colleague, she immediately points to it )


Fair enough.  Here is the DDL:

CREATE TABLE udm_asset_type_definition (
  def_id BIGSERIAL NOT NULL,
  def_name VARCHAR(32) NOT NULL,
  PRIMARY KEY (def_id)
);

When I look at the column definition, I see:

nextval('udm_asset_type_definition_def_id_seq'::regclass)

When I look at the catalog, I can see a sequence:

udm_asset_type_definition_def_id_seq

That appears identical to the column default definition and it has the 
expected 'last_value'.


Here's the odd part: If I issue

SELECT pg_get_serial_sequence('udm_asset_type_definition','def_id')

I get back NULL (doesn't matter if I qualify with schema - everything is 
in a schema called 'main' and that is first on the search path).  All 
other sequences in the database (created exactly the same way, through 
definition as 'BIGSERIAL' type) are properly found.


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_asset_type_definition','def_id'))

? I've already established that the inner expression evaluates to NULL!


It shouldn't be, this I why several perople are requesting to see the
relevant code. Experience says lots of this fails are pilot error.

As an aside, with recent postgres versions you can normally use the
returning construct to grab autogenerated id. I.e., instead of "insert
blah-blah-blah, select currval(), whatever else" you can many times do
"insert balh-blah-blah returning auto_gen_column, whatever else". I've
used it a lot, and normally leads to shorter/easier/faster code.


Yes, I changed the code to 'INSERT .. RETURNING ..' and that works 
correctly.  But, again, not necessary for any of the other tables.


This problem is not a transient fluke - I can reproduce it in two 
different databases on different servers that were created with the same 
DDL.


--

RE: BDR, ERROR: previous init failed, manual cleanup is required

[Zhu, Joshua]

Thanks a lot for your reply…

I’ve already gone with the extreme route of dropping/recreating the database 
before trying the bdr_connections_changed() call, which I’ll keep in mind next 
time when the same issue happens.

From: Dan Wierenga [mailto:dwiere...@gmail.com]
Sent: Wednesday, February 07, 2018 1:45 PM
To: Zhu, Joshua 
Cc: pgsql-gene...@postgresql.org
Subject: Re: BDR, ERROR: previous init failed, manual cleanup is required


On Wed, Feb 7, 2018 at 9:14 AM, Zhu, Joshua 
mailto:j...@vormetric.com>> wrote:

Here is a BDR problem we ran into recently:

A BDR group with a pair of nodes, N1 and N2, group is created on N1, N2 joins 
the group, so far so good
N2 departs/rejoins the group a couple of times, then ran into an issue, with 
the following symptom, after executing bdr.bdr_group_join() on N2 wrt N1:

FWIW, I was never able to successfully join a node with bdr.bdr_group_join.   I 
was only ever able to get it to work by using bdr_init_copy and letting it 
create the database on the target node for me. Run "SELECT 
bdr.bdr_node_join_wait_for_ready();" to make sure it bootstrapped properly.

I can't access my bdr cluster right now, but off the top of my head:
- check the bdr.bdr_connections table in addition to the nodes table.
- make sure you run "select bdr.bdr_connections_changed();" after you manually 
delete from any of the bdr tables.

Re: Odd behavior with 'currval'

[Adrian Klaver]

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
colleague, she immediately points to it )


Fair enough.  Here is the DDL:

CREATE TABLE udm_asset_type_definition (
   def_id BIGSERIAL NOT NULL,
   def_name VARCHAR(32) NOT NULL,
   PRIMARY KEY (def_id)
);

When I look at the column definition, I see:

nextval('udm_asset_type_definition_def_id_seq'::regclass)

When I look at the catalog, I can see a sequence:

udm_asset_type_definition_def_id_seq

That appears identical to the column default definition and it has the 
expected 'last_value'.


Here's the odd part: If I issue

SELECT pg_get_serial_sequence('udm_asset_type_definition','def_id')


What if you do?:

SELECT * FROM udm_asset_type_definition_def_id_seq;

SELECT currval('udm_asset_type_definition_id_seq');


Also what happens if you do:

pg_dump -d db_name -U some_user -s  -t udm_asset_type_definition > out.sql



I get back NULL (doesn't matter if I qualify with schema - everything is 
in a schema called 'main' and that is first on the search path).  All 
other sequences in the database (created exactly the same way, through 
definition as 'BIGSERIAL' type) are properly found.


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_asset_type_definition','def_id'))


? I've already established that the inner expression evaluates to NULL!


It shouldn't be, this I why several perople are requesting to see the
relevant code. Experience says lots of this fails are pilot error.

As an aside, with recent postgres versions you can normally use the
returning construct to grab autogenerated id. I.e., instead of "insert
blah-blah-blah, select currval(), whatever else" you can many times do
"insert balh-blah-blah returning auto_gen_column, whatever else". I've
used it a lot, and normally leads to shorter/easier/faster code.


Yes, I changed the code to 'INSERT .. RETURNING ..' and that works 
correctly.  But, again, not necessary for any of the other tables.


This problem is not a transient fluke - I can reproduce it in two 
different databases on different servers that were created with the same 
DDL.





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

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

[Adrian Klaver]

On 02/08/2018 09:38 AM, 邓彪 wrote:

i test psql on local and remote server are the same！
the jdbc remote client is display data on web page！



To me that indicates there is overhead in the Java/JDBC/Web stack that 
causing the statement to take extra time to finish.



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

Re: Odd behavior with 'currval'

[Melvin Davidson]

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 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
>>> colleague, she immediately points to it )
>>>
>>
>> Fair enough.  Here is the DDL:
>>
>> CREATE TABLE udm_asset_type_definition (
>>def_id BIGSERIAL NOT NULL,
>>def_name VARCHAR(32) NOT NULL,
>>PRIMARY KEY (def_id)
>> );
>>
>> When I look at the column definition, I see:
>>
>> nextval('udm_asset_type_definition_def_id_seq'::regclass)
>>
>> When I look at the catalog, I can see a sequence:
>>
>> udm_asset_type_definition_def_id_seq
>>
>> That appears identical to the column default definition and it has the
>> expected 'last_value'.
>>
>> Here's the odd part: If I issue
>>
>> SELECT pg_get_serial_sequence('udm_asset_type_definition','def_id')
>>
>
> What if you do?:
>
> SELECT * FROM udm_asset_type_definition_def_id_seq;
>
> SELECT currval('udm_asset_type_definition_id_seq');
>
>
> Also what happens if you do:
>
> pg_dump -d db_name -U some_user -s  -t udm_asset_type_definition > out.sql
>
>
>
>> I get back NULL (doesn't matter if I qualify with schema - everything is
>> in a schema called 'main' and that is first on the search path).  All other
>> sequences in the database (created exactly the same way, through definition
>> as 'BIGSERIAL' type) are properly found.
>>
>> 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
>> set_type_definition','def_id'))
>>
>> ? I've already established that the inner expression evaluates to NULL!
>>
>> It shouldn't be, this I why several perople are requesting to see the
>>> relevant code. Experience says lots of this fails are pilot error.
>>>
>>> As an aside, with recent postgres versions you can normally use the
>>> returning construct to grab autogenerated id. I.e., instead of "insert
>>> blah-blah-blah, select currval(), whatever else" you can many times do
>>> "insert balh-blah-blah returning auto_gen_column, whatever else". I've
>>> used it a lot, and normally leads to shorter/easier/faster code.
>>>
>>
>> Yes, I changed the code to 'INSERT .. RETURNING ..' and that works
>> correctly.  But, again, not necessary for any of the other tables.
>>
>> This problem is not a transient fluke - I can reproduce it in two
>> different databases on different servers that were created with the same
>> DDL.
>>
>>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>
>









*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
Function
Return Type Descriptioncurrval(regclass) bigint
Return value most recently obtained with nextval for specified sequence*--

*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

Re: Odd behavior with 'currval'

[Steven Hirsch]

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_seq21  1   1   
9223372036854775807 1   1   32  false   true



SELECT currval('udm_asset_type_definition_id_seq');


I get:

[Code: , SQL State: 42P01]  ERROR: relation 
"udm_asset_type_definition_id_seq" does not exist

  Position: 16


Also what happens if you do:

pg_dump -d db_name -U some_user -s  -t udm_asset_type_definition > out.sql


This:

--
-- Name: udm_asset_type_definition; Type: TABLE; Schema: main; Owner: 
asset_registry_admins

--

CREATE TABLE udm_asset_type_definition (
def_id bigint DEFAULT 
nextval('udm_asset_type_definition_def_id_seq'::regclass) NOT NULL,
def_name character varying(32) NOT NULL,
);


ALTER TABLE udm_asset_type_definition OWNER TO asset_registry_admins;

--
-- Name: udm_asset_type_definition udm_asset_type_definition_def_name_key; 
Type: CONSTRAINT; Schema: main; Owner: asset_registry_admins

--

ALTER TABLE ONLY udm_asset_type_definition
ADD CONSTRAINT udm_asset_type_definition_def_name_key UNIQUE 
(def_name);


--
-- Name: udm_asset_type_definition; Type: ACL; Schema: main; Owner: 
asset_registry_admins

--

GRANT SELECT ON TABLE udm_asset_type_definition TO asset_registry_readers;
GRANT ALL ON TABLE udm_asset_type_definition TO asset_registry_writers;




--

Re: Odd behavior with 'currval'

[Melvin Davidson]

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 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
 colleague, she immediately points to it )

>>>
>>> Fair enough.  Here is the DDL:
>>>
>>> CREATE TABLE udm_asset_type_definition (
>>>def_id BIGSERIAL NOT NULL,
>>>def_name VARCHAR(32) NOT NULL,
>>>PRIMARY KEY (def_id)
>>> );
>>>
>>> When I look at the column definition, I see:
>>>
>>> nextval('udm_asset_type_definition_def_id_seq'::regclass)
>>>
>>> When I look at the catalog, I can see a sequence:
>>>
>>> udm_asset_type_definition_def_id_seq
>>>
>>> That appears identical to the column default definition and it has the
>>> expected 'last_value'.
>>>
>>> Here's the odd part: If I issue
>>>
>>> SELECT pg_get_serial_sequence('udm_asset_type_definition','def_id')
>>>
>>
>> What if you do?:
>>
>> SELECT * FROM udm_asset_type_definition_def_id_seq;
>>
>> SELECT currval('udm_asset_type_definition_id_seq');
>>
>>
>> Also what happens if you do:
>>
>> pg_dump -d db_name -U some_user -s  -t udm_asset_type_definition > out.sql
>>
>>
>>
>>> I get back NULL (doesn't matter if I qualify with schema - everything is
>>> in a schema called 'main' and that is first on the search path).  All other
>>> sequences in the database (created exactly the same way, through definition
>>> as 'BIGSERIAL' type) are properly found.
>>>
>>> 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
>>> set_type_definition','def_id'))
>>>
>>> ? I've already established that the inner expression evaluates to NULL!
>>>
>>> It shouldn't be, this I why several perople are requesting to see the
 relevant code. Experience says lots of this fails are pilot error.

 As an aside, with recent postgres versions you can normally use the
 returning construct to grab autogenerated id. I.e., instead of "insert
 blah-blah-blah, select currval(), whatever else" you can many times do
 "insert balh-blah-blah returning auto_gen_column, whatever else". I've
 used it a lot, and normally leads to shorter/easier/faster code.

>>>
>>> Yes, I changed the code to 'INSERT .. RETURNING ..' and that works
>>> correctly.  But, again, not necessary for any of the other tables.
>>>
>>> This problem is not a transient fluke - I can reproduce it in two
>>> different databases on different servers that were created with the same
>>> DDL.
>>>
>>>
>>
>> --
>> Adrian Klaver
>> adrian.kla...@aklaver.com
>>
>>
>
>
>
>
>
>
>
>
>
> *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
> Function
> Return Type Descriptioncurrval(regclass) bigint
> Return value most recently obtained with nextval for specified sequence*--
>
> *Melvin Davidson*
> I reserve the right to fantasize.  Whether or not you
> wish to share my fantasy is entirely up to you.
>







*FYI, as an alternative, you can also do:SELECT last_valueFROM
udm_asset_type_definition_def_id_seq;*

*That should always work.*


-- 
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

Re: Odd behavior with 'currval'

[Adrian Klaver]

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 currval('udm_asset_type_definition_id_seq');


Arrgh my mistake, the above should have been

SELECT currval('udm_asset_type_definition_def_id_seq');



I get:

[Code: , SQL State: 42P01]  ERROR: relation 
"udm_asset_type_definition_id_seq" does not exist

   Position: 16


Also what happens if you do:

pg_dump -d db_name -U some_user -s  -t udm_asset_type_definition > 
out.sql


This:

--
-- Name: udm_asset_type_definition; Type: TABLE; Schema: main; Owner: 
asset_registry_admins

--

CREATE TABLE udm_asset_type_definition (
     def_id bigint DEFAULT 
nextval('udm_asset_type_definition_def_id_seq'::regclass) NOT NULL,

     def_name character varying(32) NOT NULL,
);


ALTER TABLE udm_asset_type_definition OWNER TO asset_registry_admins;

--
-- Name: udm_asset_type_definition 
udm_asset_type_definition_def_name_key; Type: CONSTRAINT; Schema: main; 
Owner: asset_registry_admins

--

ALTER TABLE ONLY udm_asset_type_definition
     ADD CONSTRAINT udm_asset_type_definition_def_name_key UNIQUE 
(def_name);


--
-- Name: udm_asset_type_definition; Type: ACL; Schema: main; Owner: 
asset_registry_admins

--

GRANT SELECT ON TABLE udm_asset_type_definition TO asset_registry_readers;
GRANT ALL ON TABLE udm_asset_type_definition TO asset_registry_writers;







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

Re: Odd behavior with 'currval'

[Adrian Klaver]

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 below show?:

\d udm_asset_type_definition_def_id_seq





SELECT currval('udm_asset_type_definition_id_seq');


I get:

[Code: , SQL State: 42P01]  ERROR: relation 
"udm_asset_type_definition_id_seq" does not exist

   Position: 16


Also what happens if you do:

pg_dump -d db_name -U some_user -s  -t udm_asset_type_definition > 
out.sql


This:

--
-- Name: udm_asset_type_definition; Type: TABLE; Schema: main; Owner: 
asset_registry_admins

--

CREATE TABLE udm_asset_type_definition (
     def_id bigint DEFAULT 
nextval('udm_asset_type_definition_def_id_seq'::regclass) NOT NULL,

     def_name character varying(32) NOT NULL,
);


ALTER TABLE udm_asset_type_definition OWNER TO asset_registry_admins;

--
-- Name: udm_asset_type_definition 
udm_asset_type_definition_def_name_key; Type: CONSTRAINT; Schema: main; 
Owner: asset_registry_admins

--

ALTER TABLE ONLY udm_asset_type_definition
     ADD CONSTRAINT udm_asset_type_definition_def_name_key UNIQUE 
(def_name);


--
-- Name: udm_asset_type_definition; Type: ACL; Schema: main; Owner: 
asset_registry_admins

--

GRANT SELECT ON TABLE udm_asset_type_definition TO asset_registry_readers;
GRANT ALL ON TABLE udm_asset_type_definition TO asset_registry_writers;







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

Re: Odd behavior with 'currval'

[Melvin Davidson]

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:
>>
>> udm_asset_type_definition_def_id_seq2111
>> 92233720368547758071132falsetrue
>>
>>
>> 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');
>
>
>
>> I get:
>>
>> [Code: , SQL State: 42P01]  ERROR: relation 
>> "udm_asset_type_definition_id_seq"
>> does not exist
>>Position: 16
>>
>> Also what happens if you do:
>>>
>>> pg_dump -d db_name -U some_user -s  -t udm_asset_type_definition >
>>> out.sql
>>>
>>
>> This:
>>
>> --
>> -- Name: udm_asset_type_definition; Type: TABLE; Schema: main; Owner:
>> asset_registry_admins
>> --
>>
>> CREATE TABLE udm_asset_type_definition (
>>  def_id bigint DEFAULT 
>> nextval('udm_asset_type_definition_def_id_seq'::regclass)
>> NOT NULL,
>>  def_name character varying(32) NOT NULL,
>> );
>>
>>
>> ALTER TABLE udm_asset_type_definition OWNER TO asset_registry_admins;
>>
>> --
>> -- Name: udm_asset_type_definition udm_asset_type_definition_def_name_key;
>> Type: CONSTRAINT; Schema: main; Owner: asset_registry_admins
>> --
>>
>> ALTER TABLE ONLY udm_asset_type_definition
>>  ADD CONSTRAINT udm_asset_type_definition_def_name_key UNIQUE
>> (def_name);
>>
>> --
>> -- Name: udm_asset_type_definition; Type: ACL; Schema: main; Owner:
>> asset_registry_admins
>> --
>>
>> GRANT SELECT ON TABLE udm_asset_type_definition TO asset_registry_readers;
>> GRANT ALL ON TABLE udm_asset_type_definition TO asset_registry_writers;
>>
>>
>>
>>
>>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>
>
*FYI, further down in Sequence Manipulation Functions is*

*" **currval*

*Return the value most recently obtained by nextval for this sequence in
the current session. (An error is reported if nextval has never been called
for this sequence in this session.) Because this is returning a
session-local value, it gives a predictable answer whether or not other
sessions have executed nextval since the current session did.*

* " *

-- 
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

Re: Odd behavior with 'currval'

[Francisco Olarte]

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, make a typo, go over it for half an hour, grab a
>> colleague, she immediately points to it )
> Fair enough.  Here is the DDL:

... nice and fast response, good explanation. Although it doesn't seem
to be ( due to the formatting ) the actual JDBC code chunks. I meant
that ones for the more eyeballs thing because you may have an actual
typo in them. Given you have taken a nice effort I suppose you have
already triple checked them, so you do not have something like

stmt.execute("select currval('uMD_asset_type_definition..

or a more devious one.

.
> SELECT pg_get_serial_sequence('udm_asset_type_definition','def_id')
> I get back NULL (doesn't matter if I qualify with schema - everything is in
> a schema called 'main' and that is first on the search path).  All other
> sequences in the database (created exactly the same way, through definition
> as 'BIGSERIAL' type) are properly found.

That is certainly strange. Seems like the sequence wasn't created by
serial code.

I would try to look carefully at the dumps and insure the columns /
schemas are all correct, that nothing has fallen  into the public
schema instead of the min one you told us you used. By I do not
remember the exact commands, it should not be too difficult.

Try comparing the definitions of this sequence and a working one in
the catalogs, and the definition of the associated columns, to see if
any field varies, I would suspect something like this.



> Yes, I changed the code to 'INSERT .. RETURNING ..' and that works
> correctly.  But, again, not necessary for any of the other tables.

I pointed it not as a necesity, but as a normally better way to grab
autogenerated id's, as this is a useful way to get the values inserted
by defaults without having to know what expression is in the default,
and even when the expression is non-repeateable, something like
"default generate_random_uuid()", or when you insert more than one
row.

The problem with the sequence must be solved, even if I convince you
my way is better and you stop using it. I would never be in peace
knowing I have a malfunctioning sequence in the database.

Francisco Olarte.

Re: Odd behavior with 'currval'

[Steven Hirsch]

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 returning zero.  The code is identical to that 
used in all other tables in the schema - all of them work.  This one does 
not - unless I use the INSERT .. RETURNING ..  approach.


I suspect the weirdness with the sequence not being found is part of the 
underlying problem.



--

Re: Odd behavior with 'currval'

[Adrian Klaver]

On 02/08/2018 10:45 AM, Steven Hirsch wrote:

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 returning zero.  The code is identical to that 
used in all other tables in the schema - all of them work.  This one 
does not - unless I use the INSERT .. RETURNING ..  approach.


I suspect the weirdness with the sequence not being found is part of the 
underlying problem.


I suspect that also, hence my previous suggestion:

\d udm_asset_type_definition_def_id_seq

This should show whether the sequence is 'owned' by the table.column.







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

Re: Odd behavior with 'currval'

[Steven Hirsch]

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 sequence 
"udm_asset_type_definition_def_id_seq" is not yet defined in this session


Which is what I'd expect at the psql command line.

In real-life, I am calling from JDBC in a single session - in fact, from 
inside a single transaction.


I will post the JDBC code as soon as I can cut it down to the pertinent 
area.


Also, since you request it:

[hirschs@ui24-lin ~]$ psql -d asset_registry -U hirschs
psql (9.6.5)
Type "help" for help.

asset_registry=# \d udm_asset_type_definition_def_id_seq
  Sequence "main.udm_asset_type_definition_def_id_seq"
Column |  Type   |Value
---+-+--
 sequence_name | name| udm_asset_type_definition_def_id_seq
 last_value| bigint  | 21
 start_value   | bigint  | 1
 increment_by  | bigint  | 1
 max_value | bigint  | 9223372036854775807
 min_value | bigint  | 1
 cache_value   | bigint  | 1
 log_cnt   | bigint  | 32
 is_cycled | boolean | f
 is_called | boolean | t

To me, the greater mystery is why 'pg_get_serial_sequence' is unable to 
find that sequence when invoked by table + column.



--

Re: Odd behavior with 'currval'

[Adrian Klaver]

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 sequence 
"udm_asset_type_definition_def_id_seq" is not yet defined in this session


Which is what I'd expect at the psql command line.

In real-life, I am calling from JDBC in a single session - in fact, from 
inside a single transaction.


I will post the JDBC code as soon as I can cut it down to the pertinent 
area.


Also, since you request it:

[hirschs@ui24-lin ~]$ psql -d asset_registry -U hirschs
psql (9.6.5)
Type "help" for help.

asset_registry=# \d udm_asset_type_definition_def_id_seq
   Sequence "main.udm_asset_type_definition_def_id_seq"
     Column |  Type   |    Value
---+-+--
  sequence_name | name    | udm_asset_type_definition_def_id_seq
  last_value    | bigint  | 21
  start_value   | bigint  | 1
  increment_by  | bigint  | 1
  max_value | bigint  | 9223372036854775807
  min_value | bigint  | 1
  cache_value   | bigint  | 1
  log_cnt   | bigint  | 32
  is_cycled | boolean | f
  is_called | boolean | t


For comparison, from one of my databases:

hplc=> \d student_attendance_attendance_id_seq
 Sequence "public.student_attendance_attendance_id_seq"
Column |  Type   |Value
---+-+--
 sequence_name | name| student_attendance_attendance_id_seq
 last_value| bigint  | 39590
 start_value   | bigint  | 1
 increment_by  | bigint  | 1
 max_value | bigint  | 9223372036854775807
 min_value | bigint  | 1
 cache_value   | bigint  | 1
 log_cnt   | bigint  | 0
 is_cycled | boolean | f
 is_called | boolean | t
Owned by: public.student_attendance.attendance_id




To me, the greater mystery is why 'pg_get_serial_sequence' is unable to 
find that sequence when invoked by table + column.


Assuming you showed the complete output I am not seeing the Owned by: 
for your sequence. I would do the \d on one of your sequences that 
'works', I am guessing you will see Owned by: .


To correct see:

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

"OWNED BY table_name.column_name
OWNED BY NONE

The OWNED BY option causes the sequence to be associated with a 
specific table column, such that if that column (or its whole table) is 
dropped, the sequence will be automatically dropped as well. If 
specified, this association replaces any previously specified 
association for the sequence. The specified table must have the same 
owner and be in the same schema as the sequence. Specifying OWNED BY 
NONE removes any existing association, making the sequence “free-standing”.

"







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

Re: Odd behavior with 'currval'

[Steven Hirsch]

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| student_attendance_attendance_id_seq
last_value| bigint  | 39590
start_value   | bigint  | 1
increment_by  | bigint  | 1
max_value | bigint  | 9223372036854775807
min_value | bigint  | 1
cache_value   | bigint  | 1
log_cnt   | bigint  | 0
is_cycled | boolean | f
is_called | boolean | t
Owned by: public.student_attendance.attendance_id



Assuming you showed the complete output I am not seeing the Owned by: for 
your sequence. I would do the \d on one of your sequences that 'works', I am 
guessing you will see Owned by: .


To correct see:

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

"OWNED BY table_name.column_name
OWNED BY NONE

   The OWNED BY option causes the sequence to be associated with a specific 
table column, such that if that column (or its whole table) is dropped, the 
sequence will be automatically dropped as well. If specified, this 
association replaces any previously specified association for the sequence. 
The specified table must have the same owner and be in the same schema as the 
sequence. Specifying OWNED BY NONE removes any existing association, making 
the sequence “free-standing”.


Agggh.  That's it!  I'll fix the ownership.

So, a few questions:

1. How on earth did this happen? I do not recall doing any manual fiddling 
with either database - they were (as far as I know) built from the same 
DDL. We may never have an answer for this.  Being human, who knows what I 
may or may not have done 4 months ago...


But,

2. Why is the currval() function being so blasted dumb?  If 
'pg_get_serial_sequence' cannot resolve the sequence, it returns NULL. 
As such, shouldn't the outer currval() also be returning NULL?  I cannot 
imagine a rationale for the current behavior.


THANKS to everyone who chimed in on this.  I was beginning to think I was 
losing my marbles.


--

Re: Odd behavior with 'currval'

[David G. Johnston]

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
> set_type_definition','def_id'))
>
> ? I've already established that the inner expression evaluates to NULL!


​This is indeed unusual...to be specific here pg_get_serial_sequence
returns null in lieu of an error for being unable to locate the indicated
sequence.  currval is returning null because it is defined "STRICT" and so
given a null input it will always return null.  currval itself, when
provided a non-null input, is going to error or provide a number (which
should never be zero...).

I'm wondering whether someone didn't like the fact that currval errors and
instead wrote a overriding function that instead returns zero?

David J.

Re: Odd behavior with 'currval'

[Adrian Klaver]

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
---+-+--
sequence_name | name    | student_attendance_attendance_id_seq
last_value    | bigint  | 39590
start_value   | bigint  | 1
increment_by  | bigint  | 1
max_value | bigint  | 9223372036854775807
min_value | bigint  | 1
cache_value   | bigint  | 1
log_cnt   | bigint  | 0
is_cycled | boolean | f
is_called | boolean | t
Owned by: public.student_attendance.attendance_id



Assuming you showed the complete output I am not seeing the Owned by: 
for your sequence. I would do the \d on one of your sequences that 
'works', I am guessing you will see Owned by: .


To correct see:

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

"OWNED BY table_name.column_name
OWNED BY NONE

   The OWNED BY option causes the sequence to be associated with a 
specific table column, such that if that column (or its whole table) 
is dropped, the sequence will be automatically dropped as well. If 
specified, this association replaces any previously specified 
association for the sequence. The specified table must have the same 
owner and be in the same schema as the sequence. Specifying OWNED BY 
NONE removes any existing association, making the sequence 
“free-standing”.


Agggh.  That's it!  I'll fix the ownership.

So, a few questions:

1. How on earth did this happen? I do not recall doing any manual 
fiddling with either database - they were (as far as I know) built from 
the same DDL. We may never have an answer for this.  Being human, who 
knows what I may or may not have done 4 months ago...


https://www.postgresql.org/docs/10/static/datatype-numeric.html#DATATYPE-SERIAL

"The data types smallserial, serial and bigserial are not true types, 
but merely a notational convenience for creating unique identifier 
columns (similar to the AUTO_INCREMENT property supported by some other 
databases). In the current implementation, specifying:


CREATE TABLE tablename (
colname SERIAL
);

is equivalent to specifying:

CREATE SEQUENCE tablename_colname_seq;
CREATE TABLE tablename (
colname integer NOT NULL DEFAULT nextval('tablename_colname_seq')
);
ALTER SEQUENCE tablename_colname_seq OWNED BY tablename.colname;
"

So the only way I know this can happen is:

ALTER SEQUENCE seq_name OWNED BY NONE;



But,

2. Why is the currval() function being so blasted dumb?  If 
'pg_get_serial_sequence' cannot resolve the sequence, it returns NULL. 
As such, shouldn't the outer currval() also be returning NULL?  I cannot 
imagine a rationale for the current behavior.


Someone else will need to comment on this as I have no idea.



THANKS to everyone who chimed in on this.  I was beginning to think I 
was losing my marbles.





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

Re: Odd behavior with 'currval'

[Steven Hirsch]

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 currval( 
pg_get_serial_sequence('udm_asset_type_definition','def_id'))

  ? I've already established that the inner expression evaluates to NULL!


​This is indeed unusual...to be specific here pg_get_serial_sequence returns 
null in lieu of an error for
being unable to locate the indicated sequence.  currval is returning null because it is 
defined "STRICT" and
so given a null input it will always return null.  currval itself, when 
provided a non-null input, is going
to error or provide a number (which should never be zero...).



I'm wondering whether someone didn't like the fact that currval errors and 
instead wrote a overriding
function that instead returns zero?


Do you mean "someone" on the PostgreSQL development team - or "someone" at 
my end?  I can assure you there are no overriding functions in either 
of my databases.  I just double-checked this.  The only 'currval' 
procedure is the one defined at installation (in public).


Looks like I may have encountered a legitimate bug?

--

Re: Odd behavior with 'currval'

[David G. Johnston]

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 returns NULL.
>>   So, where is the '0' coming from when I do:
>>
>>   SELECT currval( pg_get_serial_sequence('udm_as
>> set_type_definition','def_id'))
>>
>>   ? I've already established that the inner expression evaluates to
>> NULL!
>>
>>
>> ​This is indeed unusual...to be specific here pg_get_serial_sequence
>> returns null in lieu of an error for
>> being unable to locate the indicated sequence.  currval is returning null
>> because it is defined "STRICT" and
>> so given a null input it will always return null.  currval itself, when
>> provided a non-null input, is going
>> to error or provide a number (which should never be zero...).
>>
>
> I'm wondering whether someone didn't like the fact that currval errors and
>> instead wrote a overriding
>> function that instead returns zero?
>>
>
> Do you mean "someone" on the PostgreSQL development team - or "someone" at
> my end?  I can assure you there are no overriding functions in either of my
> databases.  I just double-checked this.  The only 'currval' procedure is
> the one defined at installation (in public).
>
> Looks like I may have encountered a legitimate bug?
>

​Yes, I meant locally.

If you can generate a standalone reproducing test script it would indeed be
treated as a bug report and looked into.  It would ideally be in psql, not
a Java program.

David J.

Re: Odd behavior with 'currval'

[David G. Johnston]

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

DOW is 0-based?

[Igal @ Lucee.org]

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

https://www.postgresql.org/docs/current/static/functions-datetime.html

Thanks,

Igal Sapir
Lucee Core Developer
Lucee.org 

Re: DOW is 0-based?

[Igor Korot]

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);
>
> https://www.postgresql.org/docs/current/static/functions-datetime.html

https://en.wikipedia.org/wiki/Determination_of_the_day_of_the_week

Thank you.

>
> Thanks,
>
> Igal Sapir
> Lucee Core Developer
> Lucee.org

Re: Odd behavior with 'currval'

[Steven Hirsch]

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 "pg_catalog", not 
"public" ...


??

All I can tell you is that when I connect from dbVisualizer and open the 
twisty under 'main.procedures' I see 100+ functions that are intrinsic to 
pgsql - currval() included.  I have almost no experience writing pgsql 
procs and absolutely never installed anything that would override the base 
function.




--

Re: Odd behavior with 'currval'

[David G. Johnston]

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
>> installation (in public).
>>
>>
>> ​So, the installed version of currval would be defined in "pg_catalog",
>> not "public" ...
>>
>
> ??
>
> All I can tell you is that when I connect from dbVisualizer and open the
> twisty under 'main.procedures' I see 100+ functions that are intrinsic to
> pgsql - currval() included.  I have almost no experience writing pgsql
> procs and absolutely never installed anything that would override the base
> function.
>

​Just to be certain, what does "\dfS+ currval" output in psql?

I'll agree this would be highly unusual but I so would this being a bug.
And the oddity with the lost sequence ownership...

David J.
​

Re: Odd behavior with 'currval'

[Steven Hirsch]

On Thu, 8 Feb 2018, David G. Johnston wrote:

new_db=# \dfS+ currval

List of functions
   Schema   |  Name   | Result data type | Argument data types |  Type  | 
Volatility | Parallel |  Owner   | Security | Access privileges | Language 
| Source code |  Des

cription
+-+--+-+++--+--+--+---+--+-+-
---
 pg_catalog | currval | bigint   | regclass| normal | 
volatile   | unsafe   | postgres | invoker  |   | internal 
| currval_oid | sequence

 current value
(1 row)

I'll agree this would be highly unusual but I so would this being a 
bug.  And the oddity with the lost sequence ownership...


So, I believe I have gotten to the bottom of the issue.  Your suggestion 
to stay within psql was the secret.  Not too surprisingly, when I run:


--- code 

\pset null '(null)'

CREATE TABLE bugtest (
  id BIGSERIAL NOT NULL,
  name VARCHAR(32) NOT NULL,
  PRIMARY KEY (id)
);

INSERT INTO bugtest(name)
VALUES ('one');

SELECT currval( pg_get_serial_sequence('bugtest','id'));

INSERT INTO bugtest(name)
VALUES ('two');

SELECT currval( pg_get_serial_sequence('bugtest','id'));

ALTER SEQUENCE bugtest_id_seq
OWNED BY NONE;

INSERT INTO bugtest(name)
VALUES ('three');

SELECT currval( pg_get_serial_sequence('bugtest','id'));

--- end code ---

I see:

--- output ---

Null display is "(null)".
CREATE TABLE
INSERT 0 1
 currval
-
   1
(1 row)

INSERT 0 1
 currval
-
   2
(1 row)

ALTER SEQUENCE
INSERT 0 1
 currval
-
  (null)  < 
(1 row)

-- end output ---

The culprit is in the JDBC domain, NOT PostgreSQL!  According to the 
documentation I found, the ResultSet 'getLong()' method returns a value of 
zero when it sees NULL as an input. Why the JDBC libs don't treat this as 
an invalid numeric conversion is beyond me.


Once again, thanks to everyone who took their time to help me out on this 
issue.  It's a great reminder of the high-quality community that surrounds 
PostgreSQL.




--

Re: PITR Multiple recoveries

[Sébastien Boutté]

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 "database system is ready to accept read only
connections"
6. To Check
7. Stop Postgres
8. . some time passed
9. resync wals with latest
10. recreate recovery.conf without standby_mode + hot_standby off
11. Start Postgres
11. Wait recovery.done
12. To Check

Thanks

Sebastien




On Thu, Feb 8, 2018 at 5:11 PM, Jeff Janes  wrote:

> On Thu, Feb 8, 2018 at 5:07 AM, Sébastien Boutté <
> sebastien.bou...@gmail.com> 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 times my local
>> database:
>>
>> What i'm doing :
>> 1. Stop Postgres
>> 2. Restore Local basebackup (untar, un gzip)
>> 3. Create recovery.conf
>> 4. Start Postgres
>> 5. Wait recovery.done
>> 6. At this point, it's OK.
>>
>
> Once recovery is done and the database is open, it is now a primary.  It
> is no longer eligible for further WAL replay.
> (Unless it is was only open in standby mode.)
>
> Cheers,
>
> Jeff
>

Re: DOW is 0-based?

[Thomas Kellerer]

Igal @ Lucee.org schrieb am 08.02.2018 um 21:24:
> 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)?

Actually, the more intuitive (in this part of the world) would be: Monday = 1, 
Sunday = 7  ;)

Thomas