Re: Odd behavior with 'currval'
On Thu, Feb 8, 2018 at 8:12 PM, Steven Hirsch wrote: . > 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. Are you sure it does ? http://sqlfiddle.com/#!17/9eecb/9696 shows it returning null. ( as expected, if it is defined strict as it should and someone has already pointed it ). Are you sure you are not using pgAdmin or a similar thing which displays null as 0 in a numeric field? Francisco Olarte.
Re: Odd behavior with 'currval'
On Fri, Feb 9, 2018 at 8:27 AM, Francisco Olarte wrote: > On Thu, Feb 8, 2018 at 8:12 PM, Steven Hirsch wrote: > . > > > 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. > > Are you sure it does ? http://sqlfiddle.com/#!17/9eecb/9696 shows it > returning null. ( as expected, if it is defined strict as it should > and someone has already pointed it ). > > Are you sure you are not using pgAdmin or a similar thing which > displays null as 0 in a numeric field? You've missed a response - the observed behavior is an artifact of JDBC use. PostgreSQL+psql is working as expected. David J.
Re: Odd behavior with 'currval'
Steven: On Thu, Feb 8, 2018 at 10:52 PM, Steven Hirsch wrote: This may sound a bit harsh but: > 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. No, that's not a JDBC problem, that's pilot error. JDBC has behaved like these for ever, and you have had people chasing ghosts ( not a biggie, we are used to this ) because, instead of providing the real chunks you were running you kept saying "when I select currval() I get 0" and similar things. If you had said "when I do rs.getLong(col) I get 0", or pasted a relevant chunk of code, lots of people here would have pointed that getLong returns a primitive java long, so rs.getObject() is needed if you may get null. JDBC has been doing this forever, and they probably do it because in many cases this behaviour is useful. This also raises suspicions about your sequence ownership problems, as you may have being ( with your best intention ) pasting your code to reproduce the problems instead of the real scripts from the database. In general, when you run a code chunk for problem reporting, try to do it with psql ( which everybody has and knows how it works ) and paste the code / result directly, without much editing. This aids a lot. In fact, if you do it you may have noticed your text in psql returned an empty column in currval(null), which, knowgin it is int, means it was null ( in a string you cannot easily distinguish empty from nulls in a simple select ). Anyway, you report was quite nice and showed you put quite a bit of work in it, treat these as tips to make problem reporting / solving easier, not just as criticism. Francisco Olarte.
testing for DEFAULT insert value in ON CONFLICT DO UPDATE query
Hi Is there any way to tell if a conflicting row in an multi-line INSERT used the DEFAULT directive? I would like to be able to upsert a bunch of rows and only UPDATE the conflicting rows where the value set was not new - the way I do this for NULLable columns is to just write NULL in the INSERT subclause for the columns that I don't want to set and use SET column=CASE WHEN EXCLUDED.column IS NULL THEN tablename.column ELSE EXCLUDED.column END (or COALESCE(), if you prefer); however for NOT NULL columns with a default, I don't know how I can do this. I was hoping for something like an "IS DEFAULT" test but that didn't work. I can't just test for the default value itself because there might be times when I want to update the value to the default, overriding an existing value. I also can't simply exclude the column from the insert because for some rows I _will_ be setting the value. Am I missing something obvious, or am I going to have to change the column to accept NULLs in order to make this work? Thanks Geoff
Re: Odd behavior with 'currval'
On Fri, 9 Feb 2018, Francisco Olarte wrote: This may sound a bit harsh but: 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. No, that's not a JDBC problem, that's pilot error. Of course it is, but that doesn't change the fact that the behavior of JDBC is not helpful at all. It also doesn't change the fact that I had a very real and very confusing "disconnect" between the sequence and the table. That may well have been pilot error on my part, but was extremely subtle and unusual in its impact - as witnessed by the number of rounds we went before David hit the issue with ownership. JDBC has behaved like these for ever, and you have had people chasing ghosts ( not a biggie, we are used to this ) because, instead of providing the real chunks you were running you kept saying "when I select currval() I get 0" and similar things. If you had said "when I do rs.getLong(col) I get 0", or pasted a relevant chunk of code, lots of people here would have pointed that getLong returns a primitive java long, so rs.getObject() is needed if you may get null. JDBC has been doing this forever, and they probably do it because in many cases this behaviour is useful. Please note that I _did_ post the JDBC code after being requested to do so. It showed clearly where I was using rs.getLong() to retrieve the id. No one commented on that as being a factor. Given Java's usual strictness about converting null to numerics, I do not find it useful. I find it completely broken and orthogonal to the spirit of a type-safe language. In my opinion, this: long i = rs.getLong(n); should throw a type exception if NULL is fetched and this: Long i = rs.getLong(n); should give 'i' the value of 'null'. Getting 0 as an answer simply makes no sense at all to me. But, now that I've been burned by it, I'll certainly never make the same mistake again. I'll make other mistakes :-). This also raises suspicions about your sequence ownership problems, as you may have being ( with your best intention ) pasting your code to reproduce the problems instead of the real scripts from the database. Not following this statement. After being requested to do so, I did post the actual JDBC code. Did that not show up in the message thread? Nothing was done to edit down or elide anything. In general, when you run a code chunk for problem reporting, try to do it with psql ( which everybody has and knows how it works ) and paste the code / result directly, without much editing. This aids a lot. In fact, if you do it you may have noticed your text in psql returned an empty column in currval(null), which, knowgin it is int, means it was null ( in a string you cannot easily distinguish empty from nulls in a simple select ). Of course I noticed it! That's how I finally spotted the issue. If I was a bit more on the ball, I'd have used psql from the start. So, now that I understand the importance of doing so (and the brain-dead behavior of JDBC) I will be sure to do so in the future. Anyway, you report was quite nice and showed you put quite a bit of work in it, treat these as tips to make problem reporting / solving easier, not just as criticism. I do not take your tone as harsh. I'm willing to take responsibility where I created my own problems. I have learned that I must reduce trouble reports to issues that can be reproduced in psql. It makes sense and I'll do so in the future. --
Re: Odd behavior with 'currval'
On 02/09/2018 08:58 AM, Steven Hirsch wrote:
On Fri, 9 Feb 2018, Francisco Olarte wrote:
This may sound a bit harsh but:
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.
No, that's not a JDBC problem, that's pilot error.
Of course it is, but that doesn't change the fact that the behavior of
JDBC is not helpful at all. It also doesn't change the fact that I had
a very real and very confusing "disconnect" between the sequence and the
table. That may well have been pilot error on my part, but was
extremely subtle and unusual in its impact - as witnessed by the number
of rounds we went before David hit the issue with ownership.
JDBC has behaved like these for ever, and you have had people chasing
ghosts ( not a biggie, we are used to this ) because, instead of
providing the real chunks you were running you kept saying "when I
select currval() I get 0" and similar things. If you had said "when I
do rs.getLong(col) I get 0", or pasted a relevant chunk of code, lots
of people here would have pointed that getLong returns a primitive
java long, so rs.getObject() is needed if you may get null. JDBC has
been doing this forever, and they probably do it because in many cases
this behaviour is useful.
Please note that I _did_ post the JDBC code after being requested to do
so. It showed clearly where I was using rs.getLong() to retrieve the
id. No one commented on that as being a factor.
Given Java's usual strictness about converting null to numerics, I do
not find it useful. I find it completely broken and orthogonal to the
spirit of a type-safe language. In my opinion, this:
long i = rs.getLong(n);
should throw a type exception if NULL is fetched and this:
Long i = rs.getLong(n);
should give 'i' the value of 'null'. Getting 0 as an answer simply
makes no sense at all to me.
But, now that I've been burned by it, I'll certainly never make the same
mistake again. I'll make other mistakes :-).
This also raises suspicions about your sequence ownership problems, as
you may have being ( with your best intention ) pasting your code to
reproduce the problems instead of the real scripts from the database.
Not following this statement. After being requested to do so, I did
post the actual JDBC code. Did that not show up in the message thread?
Nothing was done to edit down or elide anything.
I remember seeing it, so I went back to look at the message. Turns out
you sent it to me only. Unfortunately I am not a Java programmer so I
did not catch the error. For the record:
"
Here is the JDBC code:
try {
conn.setAutoCommit(false);
PreparedStatement sth = null;
ResultSet rs = null;
// Insert new
sth = conn.prepareStatement(addAssetType);
sth.setString(1, name);
sth.execute();
sth = conn.prepareStatement(lastTypeId);
rs = sth.executeQuery();
if (rs.next()) {
long id = rs.getLong(1);
result.put("id", id);
result.put("name", name);
}
else {
throw new
WebApplicationException(buildError(BAD_REQUEST, "Lookup of most recent
sequence failed"));
}
conn.commit();
}
catch (SQLException e) {
conn.rollback();
throw new
WebApplicationException(buildError(BAD_REQUEST, e.getMessage()));
}
Where:
private final static String addAssetType =
"INSERT INTO udm_asset_type_definition (def_name) "
+ "VALUES (?)";
private final static String lastTypeId = "SELECT currval(
pg_get_serial_sequence('udm_asset_type_definition', 'def_id'))";
Code with this exact same structure (but different SQL, obviously) is
working just fine with five other tables using sequences. The above
code snippet always produces '0' for the id. If I use the 'INSERT ..
RETURNING..' approach, it gives the right answer.
"
In general, when you run a code chunk for problem reporting, try to do
it with psql ( which everybody has and knows how it works ) and paste
the code / result directly, without much editing. This aids a lot. In
fact, if you do it you may have noticed your text in psql returned an
empty column in currval(null), which, knowgin it is int, means it was
null ( in a string you cannot easily distinguish empty from nulls in a
simple select ).
Of course I noticed it! That's how I finally spotted the issue. If I
was a bit more on the ball, I'd have used psql from the start. So, now
that I understand the importance of doing so (and the brain-dead
behavior of JDBC) I will be sure
Re: Odd behavior with 'currval'
On Fri, 9 Feb 2018, Adrian Klaver wrote:
I remember seeing it, so I went back to look at the message. Turns out you
sent it to me only. Unfortunately I am not a Java programmer so I did not
catch the error. For the record:
Ah, blast... Apologies to everyone concerned. I need to understand why
my mail reader (Alpine on Linux) insists on defaulting to the original
poster's e-mail address when I hit 'Reply' rather than the group list.
It's not doing that with any other mail list I subscribe to.
I was trying to be careful and changed the address manually for all
messages except this one.
Thanks, Adrian! I was sure that I sent it - just not to the right place
as it turns out.
"
Here is the JDBC code:
try {
conn.setAutoCommit(false);
PreparedStatement sth = null;
ResultSet rs = null;
// Insert new
sth = conn.prepareStatement(addAssetType);
sth.setString(1, name);
sth.execute();
sth = conn.prepareStatement(lastTypeId);
rs = sth.executeQuery();
if (rs.next()) {
long id = rs.getLong(1);
result.put("id", id);
result.put("name", name);
}
else {
throw new WebApplicationException(buildError(BAD_REQUEST,
"Lookup of most recent sequence failed"));
}
conn.commit();
}
catch (SQLException e) {
conn.rollback();
throw new WebApplicationException(buildError(BAD_REQUEST,
e.getMessage()));
}
Where:
private final static String addAssetType =
"INSERT INTO udm_asset_type_definition (def_name) "
+ "VALUES (?)";
private final static String lastTypeId = "SELECT currval(
pg_get_serial_sequence('udm_asset_type_definition', 'def_id'))";
Code with this exact same structure (but different SQL, obviously) is working
just fine with five other tables using sequences. The above code snippet
always produces '0' for the id. If I use the 'INSERT .. RETURNING..'
approach, it gives the right answer.
"
--
initdb execution
Hello I have a question regrading initdb, My understanding of initdb is when we execute it it will create the directory structure in /data. My question is when we run initdb when we already have a database running and for some reason I run this command what happens to my existing actual data does it get flushed out ? In that case what is the best practice here? -- Regards, Azim
Re: Odd behavior with 'currval'
Steven:
On Fri, Feb 9, 2018 at 5:58 PM, Steven Hirsch wrote:
> On Fri, 9 Feb 2018, Francisco Olarte wrote:
>> This may sound a bit harsh but:
>>> 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.
>
>> No, that's not a JDBC problem, that's pilot error.
> Of course it is, but that doesn't change the fact that the behavior of JDBC
> is not helpful at all.
That's an opinion, perfectly valid but just that.
For me a long returning function which has to cope with the DB nulls
has two options. Return a default value, and 0 is the one I would in
this case without doubt, or throw an exception ( NullPointer,
SQLexception, checked or not ). IMO the second one is much less
helpful, given a null value on a nullable column is not an exceptional
condition, and there are plenty of methods to do it right, i.e., test
for null first read the value then, or use (Long)getObject.
> It also doesn't change the fact that I had a very
> real and very confusing "disconnect" between the sequence and the table.
> That may well have been pilot error on my part, but was extremely subtle and
> unusual in its impact - as witnessed by the number of rounds we went before
> David hit the issue with ownership.
Bear in mind the disconnect is easier to see in the catalogs, or on a
schema dump. It's extremely difficult to debug without having access
to them. And the 0 issue does not help, as it sends people on a wild
goose chase thinking something is borked there.
Given the sequence was unowned someone had to 1.- create the sequence
and table in two steps ( maybe from an edited schema dump ) or 2.-
alter it to not owned. Those are DML, those are schema definitions,
and we didn't have them.
>> JDBC has behaved like these for ever, and you have had people chasing
>> ghosts ( not a biggie, we are used to this ) because, instead of
>> providing the real chunks you were running you kept saying "when I
>> select currval() I get 0" and similar things. If you had said "when I
>> do rs.getLong(col) I get 0", or pasted a relevant chunk of code, lots
>> of people here would have pointed that getLong returns a primitive
>> java long, so rs.getObject() is needed if you may get null. JDBC has
>> been doing this forever, and they probably do it because in many cases
>> this behaviour is useful.
> Please note that I _did_ post the JDBC code after being requested to do so.
> It showed clearly where I was using rs.getLong() to retrieve the id. No one
> commented on that as being a factor.
My fault them, I failed to see it, and I'm unable to see it in my
archives, and google is not finding it so . aha, next message in
the thread, you did not sent it to everyone. Happens a lot.
> Given Java's usual strictness about converting null to numerics, I do not
> find it useful. I find it completely broken and orthogonal to the spirit of
> a type-safe language. In my opinion, this:
>
> long i = rs.getLong(n);
> should throw a type exception if NULL is fetched and this:
> Long i = rs.getLong(n);
> should give 'i' the value of 'null'. Getting 0 as an answer simply makes no
> sense at all to me.
You are, IMO, completely wrong. Java can not override on an inferred
return value type, not even on a explicit one.
So, getLong returning a long will never cast to (Long) null.
Your second behaviour is trivial, is just (Long)(rs.getObject(n)), and
I use it a lot.
If you (trivially) derive and wrap or make the driver use jour
resultSet and you put a public Long getLongX(int n) { return
(Long)getObject(n) } in it you have your method. You just cannot call
it getLong(n) ( no overrideing with different result type ).
There is probably a good reason to make it return primitives. Remember
JDBC dates from when java had no autoboxing as was really slow.
and throwing an exception is, IMO, as I wrote above, much less helpful.
> But, now that I've been burned by it, I'll certainly never make the same
> mistake again. I'll make other mistakes :-).
We all learn this way.
>> This also raises suspicions about your sequence ownership problems, as
>> you may have being ( with your best intention ) pasting your code to
>> reproduce the problems instead of the real scripts from the database.
> Not following this statement. After being requested to do so, I did post
> the actual JDBC code. Did that not show up in the message thread? Nothing
> was done to edit down or elide anything.
Commented above, common error, you did reply instead of reply to all.
>> In general, when you run a code chunk for problem reporting, try to do
>> it with psql ( which everybody has and knows how it works ) and paste
>> the code / result directly, without much editing. This aids a lot. In
>> fact, if you do it you may have noticed your text in psql returned an
>> e
Re: Odd behavior with 'currval'
Steven: On Fri, Feb 9, 2018 at 6:33 PM, Steven Hirsch wrote: > Ah, blast... Apologies to everyone concerned. I need to understand why my > mail reader (Alpine on Linux) insists on defaulting to the original poster's > e-mail address when I hit 'Reply' rather than the group list. It's not doing > that with any other mail list I subscribe to. It's probably because this list headers are "old school", without those newflangled thingied. It defaults to the sender for mine, and for nearly everyone else, too. We are just used to hit reply-all. > I was trying to be careful and changed the address manually for all messages > except this one. Do not bother with that. The style in this list is to just reply to all, so the people that are participating in the thread can get a copy of the messages in both their inbox and the list folder when filtering. In fact I told gmail to skip inbox for the list, as I read it fully normally, but if I hadn't do that and there was a traffic spike it would be easier for me to first answer the threads Iwas involved in and then reading the rest. In fact traffic is not that high, and I do reply to the messages which show "me" in the gmail list first, then the rest. Francisco Olarte.
Re: initdb execution
Azim On Fri, Feb 9, 2018 at 6:36 PM, Azimuddin Mohammed wrote: > Hello I have a question regrading initdb, > My understanding of initdb is when we execute it it will create the > directory structure in /data. > My question is when we run initdb when we already have a database running > and for some reason I run this command what happens to my existing actual > data does it get flushed out ? In that case what is the best practice here? I think initdb refuses to run when given an existent non empty directory, but cannot find anything in its doc page... Ok, after a little googling I've found "initdb will refuse to run if the data directory exists and already contains files; this is to prevent accidentally overwriting an existing installation." in the server setup / creating cluster docs ( https://www.postgresql.org/docs/10/static/creating-cluster.html ). Francisco Olarte.
Re: initdb execution
Thanks for the reply.. Let me execute in the newly built setup. On Fri, Feb 9, 2018 at 11:50 AM, Francisco Olarte wrote: > Azim > > On Fri, Feb 9, 2018 at 6:36 PM, Azimuddin Mohammed > wrote: > > Hello I have a question regrading initdb, > > My understanding of initdb is when we execute it it will create the > > directory structure in /data. > > My question is when we run initdb when we already have a database running > > and for some reason I run this command what happens to my existing actual > > data does it get flushed out ? In that case what is the best practice > here? > > I think initdb refuses to run when given an existent non empty > directory, but cannot find anything in its doc page... > > Ok, after a little googling I've found "initdb will refuse to run if > the data directory exists and already contains files; this is to > prevent accidentally overwriting an existing installation." in the > server setup / creating cluster docs ( > https://www.postgresql.org/docs/10/static/creating-cluster.html ). > > Francisco Olarte. > -- Regards, Azim
Re: Odd behavior with 'currval'
On 02/09/2018 09:43 AM, Francisco Olarte wrote: Steven: On Fri, Feb 9, 2018 at 6:33 PM, Steven Hirsch wrote: Ah, blast... Apologies to everyone concerned. I need to understand why my mail reader (Alpine on Linux) insists on defaulting to the original poster's e-mail address when I hit 'Reply' rather than the group list. It's not doing that with any other mail list I subscribe to. It's probably because this list headers are "old school", without those newflangled thingied. I believe with the change to PGLister that changed: https://wiki.postgresql.org/wiki/PGLister_Announce "PGLister will use standard, industry-recognized headers to identify mailing list messages. The non-standard header "X-Mailing-List" will no longer be included. Any users whose filters are based on this non-standard header will need to adjust their filters. " It defaults to the sender for mine, and for nearly everyone else, too. We are just used to hit reply-all. I was trying to be careful and changed the address manually for all messages except this one. Do not bother with that. The style in this list is to just reply to all, so the people that are participating in the thread can get a copy of the messages in both their inbox and the list folder when filtering. In fact I told gmail to skip inbox for the list, as I read it fully normally, but if I hadn't do that and there was a traffic spike it would be easier for me to first answer the threads Iwas involved in and then reading the rest. In fact traffic is not that high, and I do reply to the messages which show "me" in the gmail list first, then the rest. Francisco Olarte. -- Adrian Klaver adrian.kla...@aklaver.com
Re: Odd behavior with 'currval'
Adrian: On Fri, Feb 9, 2018 at 7:17 PM, Adrian Klaver wrote: > On 02/09/2018 09:43 AM, Francisco Olarte wrote: >> It's probably because this list headers are "old school", without >> those newflangled thingied. > I believe with the change to PGLister that changed: > > https://wiki.postgresql.org/wiki/PGLister_Announce > > "PGLister will use standard, industry-recognized headers to identify mailing > list messages. The non-standard header "X-Mailing-List" will no longer be > included. Any users whose filters are based on this non-standard header will > need to adjust their filters. " Yep, my reference to mailing list headers is unfortunate. Thae fact is it seems messages in the list do not have the reply-to to the lists as many other lists seem to use, no magic list headers involved at all. I.e., in another list I have From=poster, to=list, reply-to=list, in your message I have from=poster, to=me&steve, cc=list, no reply-to For what I know of muas, what you put in to/cc only does make a difference in reply-all, but witout reply-to normal reply defaults to from, so pg sends it to the OP, the others to the list. Reply-to inclusion might be better for a list, but I found the pg style is a bit confussing ( still ) but makes for nicer identification of people in message lists. Francisco Olarte.
Re: Odd behavior with 'currval'
On Fri, 9 Feb 2018, Francisco Olarte wrote: I was trying to be careful and changed the address manually for all messages except this one. Do not bother with that. The style in this list is to just reply to all, so the people that are participating in the thread can get a copy of the messages in both their inbox and the list folder when filtering. In fact I told gmail to skip inbox for the list, as I read it fully normally, but if I hadn't do that and there was a traffic spike it would be easier for me to first answer the threads Iwas involved in and then reading the rest. In fact traffic is not that high, and I do reply to the messages which show "me" in the gmail list first, then the rest. Heh. There is no winning in this situation. I have been hauled over hot coals in other mailing lists for replying to 'All' :-). --
Migrating Postgresql from Linux x86
Hi Anyone have migrated PostgreSQL running on Linux x86_64 to running on Linux on Power platform. Interested in a checklist of things to be done and if there is a proper document with the steps to completing would also be of interest. Thanks Clive A.
Re: Migrating Postgresql from Linux x86
Hi. We do that migration from Postgres 9.0 (x86_64, Centos 6) to Postgres 9.6 (power, SUSE Enterprise 12). The best way to us was to do a backup (pg_dump -Fc ...) and restore using several cores (pg_restore -j N ..., where N equals to the cores/jobs to be used). How many jobs launch to restore depends on: the number of cores available and the I/O throughput. Make some test to get the right number of jobs. Our restore task took around four hours. We had a downtime of seven hours. Other options are: * You can try a logical replication (maybe pg_logical). * Backup/restore without stop and then, make a downtime and synchronize the changed data between tables. We preferred the failsafe option and made a full downtime and we done the job from Saturday to Sunday with a holiday Monday (It is 24x7x365 business, and get a downtime is somewhat difficult). Best regards. Carlos M. On Fri, Feb 9, 2018 at 7:44 PM, Clive Anglin wrote: > > Hi > > Anyone have migrated PostgreSQL running on Linux x86_64 to running on Linux > on Power platform. Interested in a checklist of things to be done and if > there is a proper document with the steps to completing would also be of > interest. > > > > Thanks > > > > Clive A. > > > >
