Re: [GENERAL] JDBC and inet type

[rob stone]

On Fri, 2015-12-04 at 09:41 +, Tim Smith wrote:
> Hi,
> 
> When I use "preparedStatement.setString(5,ip);" to send values to a
> stored function, it obviously gets sent to postgres as "character
> varying".
> 
> Postgres obviously complains loudly and says " Hint: No function
> matches the given name and argument types. You might need to add
> explicit type casts.".
> 
> What is the appropriate workaround ?
> 
> Thanks!
> 
> Tim
> 
> 

"preparedStatement.setString(5,ip);" will set parameter number five to
the string held in the variable.
That's what setString does. It expects a string to be passed.
Your function's fifth IN argument needs to be defined as a "string".
Within the function you will have to cast it to inet.

HTH,

Rob


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] JDBC and inet type

[Bill Moran]

On Fri, 4 Dec 2015 09:41:24 +
Tim Smith  wrote:

> When I use "preparedStatement.setString(5,ip);" to send values to a
> stored function, it obviously gets sent to postgres as "character
> varying".
> 
> Postgres obviously complains loudly and says " Hint: No function
> matches the given name and argument types. You might need to add
> explicit type casts.".
> 
> What is the appropriate workaround ?

You can define param 5 as varchar in your query, as Rob suggests:

CREATE FUNCTION some_function(int, int, int, int, int, varchar) ...

Then cast the 5th parameter to INET within your function.

You can also cast the value in your SQL.

sql = "SELECT some_function($, $, $, $, $::INET)"; ...

You could also create an Inet class in Java and implement the
SQLData interface, then use setObject() instead of setString().
It doesn't appear as if anyone has done this yet, but it would
be nice if it were incluced in the JDBC driver.

The first answer is probably best for stored procedures, as it
simplifies things down the road. The second solution is more
universal, as it works for non-function-calling SQL as well.
The third solution is probably _really_ the correct one, from
a pedantic standpoint, but it's a bit more work to implement.

-- 
Bill Moran


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] JDBC and inet type

[Tim Smith]

Hi,

When I use "preparedStatement.setString(5,ip);" to send values to a
stored function, it obviously gets sent to postgres as "character
varying".

Postgres obviously complains loudly and says " Hint: No function
matches the given name and argument types. You might need to add
explicit type casts.".

What is the appropriate workaround ?

Thanks!

Tim


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] plperlu stored procedure seems to freeze for a minute

[Peter J. Holzer]

On 2015-12-03 10:02:18 -0500, Tom Lane wrote:
> "Peter J. Holzer"  writes:
> > Can those signals be safely ignored? Just blocking them (so that they
> > are delivered after the UDF finishes) might be safer. But even that may
> > be a problem: If the UDF then executes some SQL, could that rely on
> > signals being delivered? I have no idea. 
> 
> The minute you start fooling with a backend's signal behavior, we're
> going to politely refuse to support whatever breakage you run into.

As I understood Jim he was talking about possible changes to postgresql
to shield UDFs from those signals, not something the author of a UDF
should do.


> We aren't sending those signals just for amusement's sake.

Right. That's why I was sceptical whether those signals could be
ignored. I wouldn't have thought so, but Jim clearly knows a lot more
about the inner workings of postgresql than I do (which is easy - I know
almost nothing) and maybe he knows of a way (something like "we can
ignore signals while executing the UDF and just assume that we missed at
least one signal and call the magic synchronize state function
afterwards")

hp

-- 
   _  | Peter J. Holzer| I want to forget all about both belts and
|_|_) || suspenders; instead, I want to buy pants 
| |   | h...@hjp.at | that actually fit.
__/   | http://www.hjp.at/ |   -- http://noncombatant.org/


signature.asc
Description: Digital signature

Re: [GENERAL] Pgbasebackup help

[David Steele]

On 12/2/15 6:25 PM, Jim Nasby wrote:

On 12/2/15 1:56 PM, David Steele wrote:

>Also, I don’t want enable archive_mode = on as it needs to maintain
>archives files.

As it turns out, archiving would be the solution to your problem.  If
you were archiving you could restore a*previous*  backup and then replay
WAL to exactly T3.  There might be some jitter from clock differences
but it should have the desired effect.


And in this case previous could be a PG backup taken immediately before
the backup of the private DBMS.


Exactly.


I don't remember off-hand if pg_basebackup has an option for delaying
the pg_stop_backup() call, but if it does then I think the simplest
thing is to just delay that until after your private DBMS backup is
complete.


There's no option to delay it in pg_basebackup but it can be called 
whenever you like when using pg_start/stop_backup.  So, yeah, if the 
postgres backup were started first then pg_stop_backup() could be called 
right after the private backup stops.  That would make them pretty much 
in sync.


Of course, it's a lot of work to get that going.  pg_basebackup does a 
lot of work for you.


--
-David
da...@pgmasters.net


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Pgbasebackup help

[David Steele]

On 12/3/15 12:59 AM, Yelai, Ramkumar IN BLR STS wrote:

What I wanted to achieve is simple copy of Data folder. I can't shutdown the 
database during the backup and unable to use file system copy of data folder as 
it creates inconsistency and don't want to use pg_dump.

Hence I decided to use Pg_basebackup for copying the base backup and don't want 
to replay the wal.


Replaying WAL is *not* optional.  Each restore will have to replay at 
least one WAL segment to become consistent, depending on write volume 
during the backup.



Anyway, pg_basebackup put checkpoint before copying the data folder. For me it 
is enough to restore till checkpoint.


This won't work - the database keeps running and making changes after 
the checkpoint.



I saw this link 
http://blog.veritech.io/2014/10/automated-backup-for-postgresql-cluster.html.

In this link also, I have not seen they have enabled archive_mode. Archive mode 
is not necessary as long as you streaming the your wal files to pg_xlog.


These instructions are for bringing up a replica.  Even if this is OK 
for your purposes, it still would not get you a database at time T3. 
You are supposing that because this method does not use archiving that



Also, even if I have all wal files , how do I  restore till time T3. I am 
analyzing at pgbackrest to know how to restore backup till time T3.


To restore to time T3 you would select a backup that ended *before* T3 
then using point-in-time recovery to play forward to T3.


That should be explained pretty clearly in the user guide - if there's 
something you don't understand then it would be helpful to know so I can 
improve the guide.


--
-David
da...@pgmasters.net


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Table with invalid page blocks

[Gerdan Rezende dos Santos]

Someone has some way of identifying all invalid blocks of a table postgresql
?
Plpgsql a function, a tool, somehow.


I found one solution on
http://www.postgresql.org/message-id/1184245756.24101.178.ca...@coppola.muc.ecircle.de,
but I can not change in order to identify any defective blocks at once.


Postgresql 9.3.10 x64
Red Hat 7 x64


T.'.A.'.F.'.,
*Gerdan Rezende dos Santos *
+55 (61) 9645-1525

[GENERAL] Table with invalid page blocks

[Gerdan Rezende dos Santos]

Someone has some way of identifying all invalid blocks of a table postgresql
?
Plpgsql a function, a tool, somehow.


I found one solution on
http://www.postgresql.org/message-id/1184245756.24101.178.ca...@coppola.muc.ecircle.de,
but I can not change in order to identify any defective blocks at once.


Postgresql 9.3.10 x64
Red Hat 7 x64


T.'.A.'.F.'.,
*Gerdan Rezende dos Santos *
+55 (61) 9645-1525

[GENERAL] question about replication slots

[Andreas Kretschmer]

Hi @ll,

imagine a streaming replication using physical replication slots. And sometime a
fail over. All okay. I take a basebackup and rebuild the old master as slave.

Is there a risk that the new slave contains active replication slots but no
listener on it? What have i to consider?

Thx.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] BDR: ALTER statement hanging

[Selim Tuvi]

Yes they seem to be active:

deliver=# select * from pg_replication_slots;
slot_name | plugin | slot_type | datoid | 
database | active | xmin | catalog_xmin | restart_lsn 
--++---++--++--+--+-
 bdr_533136_6223770712502831127_1_16389__ | bdr| logical   | 533136 | 
deliver  | t  |  |   182302 | 0/9C8A5598  
 bdr_533136_6223800735012265413_1_16389__ | bdr| logical   | 533136 | 
deliver  | t  |  |   182302 | 0/9C8A5598  
(2 rows) 

Although when I look at bdr.bdr_nodes I see the status as still initializing 
for the other two nodes, I don't know if that could cause this problem:

deliver=# select * from bdr.bdr_nodes;
 node_sysid  | node_timeline | node_dboid | node_status |  
node_name  | 
node_local_dsn
 |  
node_init_from_dsn
-+---++-+-+---
-+--
 6212648563684174798 | 1 | 533136 | r   | 
pe-deliverdb-sf-01v | host=pe-deliverdb-sf-01v port=5432 dbname=deliver 
user=deliver_admin password=x   |
 6223770712502831127 | 1 |  16389 | i   | 
pe-deliverdb-sing-01v | host=pe-deliverdb-sing-01v port=5432 dbname=deliver 
user=deliver_admin password=x | host=pe-deliverdb-sf-01v port=5432 
dbname=deliver user=deliver_admin password=x
 6223800735012265413 | 1 |  16389 | i   | 
pe-deliverdb-lon-01v | host=pe-deliverdb-lon-01v port=5432 dbname=deliver 
user=deliver_admin password=x  | host=pe-deliverdb-sf-01v port=5432 
dbname=deliver user=deliver_admin password=x

-Selim


From: pgsql-general-ow...@postgresql.org [pgsql-general-ow...@postgresql.org] 
on behalf of Andreas Kretschmer [akretsch...@spamfence.net]
Sent: Thursday, December 03, 2015 10:49 PM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] BDR: ALTER statement hanging

Selim Tuvi  wrote:

> Hi, I am running a three node BDR cluster. BDR version is 0.9.3. Postgres
> version is 9.4.5.
>
> With 0.9.2, I used to be able to issue ALTER statements using psql and it 
> would
> go through. This time it is just hanging. The statement is this:

for ddl-commands all nodes MUST be active in replication, so have you
checked that in pg_replication_slots?



Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.  (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."   (unknown)
Kaufbach, Saxony, Germany, Europe.  N 51.05082°, E 13.56889°


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] bdr manual cleanup required

[Selim Tuvi]

I am trying to repair a broken bdr cluster setup and so far everything I tried 
failed. Under the original node that ran bdr.bdr_group_create I am getting the 
following error:

2015-12-04 19:34:29.063 UTC,,,22991,,5661eac4.59cf,1,,2015-12-04 19:34:28 
UTC,3/0,0,ERROR,55000,"previous init failed, manual cleanup is required","Found 
bdr.bdr_nodes entry for bdr (6224504646761731677,1,16389,) with state=i in 
remote bdr.bdr_nodes","Remove all replication identifiers and slots 
corresponding to this node from the init target node then drop and recreate 
this database and try again",,,"bdr (6224504646761731677,1,16389,): perdb"

Is there a way to get the cluster in a correct state without having to drop the 
db?

Thanks
-Selim

Re: [GENERAL] Pgbasebackup help

[Yelai, Ramkumar IN BLR STS]

Thanks David,

This helped me to understand the WAL importance.

Finally, we decided to use "stream" option to copy the WAL file during the 
backup as mentioned in the help.

Enabled this options in postgres.conf

wal_level = hot_standby
max_wal_senders = 2

Though I get the information, which are archived during the backup process. I 
myself deleting archived records the after time T3, while restoring the backup 
by using our sql procedures. 

Since I am not using archive recovery or standy replica (I am restoring the 
data folder in to the same server not in standby server), I can't use the 
recovery.conf options to recover till Time T3.  Hence I forcefully deleted as 
mentioned earlier.

Please let me know If any way to replay the WAL till Time T3 then I am 
interested to use it.

Thanks once again.

Regards,
Ramkumar.

-Original Message-
From: David Steele [mailto:da...@pgmasters.net] 
Sent: Friday, December 04, 2015 6:26 PM
To: Yelai, Ramkumar IN BLR STS; pgsql-general@postgresql.org
Subject: Re: [GENERAL] Pgbasebackup help

On 12/3/15 12:59 AM, Yelai, Ramkumar IN BLR STS wrote:
> What I wanted to achieve is simple copy of Data folder. I can't shutdown the 
> database during the backup and unable to use file system copy of data folder 
> as it creates inconsistency and don't want to use pg_dump.
>
> Hence I decided to use Pg_basebackup for copying the base backup and don't 
> want to replay the wal.

Replaying WAL is *not* optional.  Each restore will have to replay at least one 
WAL segment to become consistent, depending on write volume during the backup.

> Anyway, pg_basebackup put checkpoint before copying the data folder. For me 
> it is enough to restore till checkpoint.

This won't work - the database keeps running and making changes after the 
checkpoint.

> I saw this link 
> http://blog.veritech.io/2014/10/automated-backup-for-postgresql-cluster.html.
>
> In this link also, I have not seen they have enabled archive_mode. Archive 
> mode is not necessary as long as you streaming the your wal files to pg_xlog.

These instructions are for bringing up a replica.  Even if this is OK for your 
purposes, it still would not get you a database at time T3. 
You are supposing that because this method does not use archiving that

> Also, even if I have all wal files , how do I  restore till time T3. I am 
> analyzing at pgbackrest to know how to restore backup till time T3.

To restore to time T3 you would select a backup that ended *before* T3 then 
using point-in-time recovery to play forward to T3.

That should be explained pretty clearly in the user guide - if there's 
something you don't understand then it would be helpful to know so I can 
improve the guide.

--
-David
da...@pgmasters.net


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] BDR: ALTER statement hanging

[Selim Tuvi]

Thanks, I removed the other nodes from bdr.bdr_nodes table, deleted all the 
bdr_connections and pg_replication_identifier entries, dropped the 
pg_replication_slots restarted the instance and then trying the ALTER statement 
resulted in:

ERROR:  No peer nodes or peer node count unknown, cannot acquire DDL lock
HINT:  BDR is probably still starting up, wait a while  

The only way I could issue the statement is run the following to convert the 
node to a standalone instance:

BEGIN;
SET LOCAL bdr.permit_unsafe_ddl_commands = true;
SET LOCAL bdr.skip_ddl_locking = true;
security label for 'bdr' on database deliver is '{"bdr": false}';
COMMIT;

I am still puzzled as to why the bdr_nodes node_status was reporting "i" when 
there were no errors in the logs.

-Selim


From: pgsql-general-ow...@postgresql.org [pgsql-general-ow...@postgresql.org] 
on behalf of Andreas Kretschmer [andr...@a-kretschmer.de]
Sent: Friday, December 04, 2015 9:59 AM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] BDR: ALTER statement hanging

> Selim Tuvi  hat am 4. Dezember 2015 um 18:46 geschrieben:
>
>
> Yes they seem to be active:
>
> deliver=# select * from pg_replication_slots;
> slot_name | plugin | slot_type | datoid |
> database | active | xmin | catalog_xmin | restart_lsn
> --++---++--++--+--+-
>  bdr_533136_6223770712502831127_1_16389__ | bdr| logical   | 533136 |
> deliver  | t  |  |   182302 | 0/9C8A5598
>  bdr_533136_6223800735012265413_1_16389__ | bdr| logical   | 533136 |
> deliver  | t  |  |   182302 | 0/9C8A5598
> (2 rows)
>
> Although when I look at bdr.bdr_nodes I see the status as still initializing
> for the other two nodes, I don't know if that could cause this problem:
>
> deliver=# select * from bdr.bdr_nodes;
>  node_sysid  | node_timeline | node_dboid | node_status |
>  node_name  |
> node_local_dsn
>  |
>  node_init_from_dsn
> -+---++-+-+---
> -+--
>  6212648563684174798 | 1 | 533136 | r   |
> pe-deliverdb-sf-01v | host=pe-deliverdb-sf-01v port=5432 dbname=deliver
> user=deliver_admin password=x   |
>  6223770712502831127 | 1 |  16389 | i   |
> pe-deliverdb-sing-01v | host=pe-deliverdb-sing-01v port=5432 dbname=deliver
> user=deliver_admin password=x | host=pe-deliverdb-sf-01v port=5432
> dbname=deliver user=deliver_admin password=x
>  6223800735012265413 | 1 |  16389 | i   |
> pe-deliverdb-lon-01v | host=pe-deliverdb-lon-01v port=5432 dbname=deliver
> user=deliver_admin password=x  | host=pe-deliverdb-sf-01v port=5432
> dbname=deliver user=deliver_admin password=x
>
> -Selim
>


I think, the state 'i' is the main reason for your problem, because of: "i-
Joining: The node is doing initial slot creation or an initial dump and load".

But i can't tell you why this nodes are in this state.


Regards, Andreas


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] BDR: ALTER statement hanging

[Selim Tuvi]

Yes, bdr_connections had the same number of rows:

deliver=# select * from bdr.bdr_connections;
 conn_sysid  | conn_timeline | conn_dboid | conn_origin_sysid | 
conn_origin_timeline | conn_origin_dboid | conn_is_unidirectional | 
  
 conn_dsn| 
conn_apply_delay | conn_replication_sets
   
-+---++---+--+---++---
-+--+---
  
 6212648563684174798 | 1 | 533136 | 0 | 
   0 | 0 | f  | 
host=pe-deliverdb-sf-01v port=5432 dbname=deliver user=deliver_admin 
password=x   |  | {default} 
  
 6223770712502831127 | 1 |  16389 | 0 | 
   0 | 0 | f  | 
host=pe-deliverdb-sing-01v port=5432 dbname=deliver user=deliver_admin 
password=x |  | {default}   

 6223800735012265413 | 1 |  16389 | 0 | 
   0 | 0 | f  | 
host=pe-deliverdb-lon-01v port=5432 dbname=deliver user=deliver_admin 
password=x  |  | {default}  
 
(3 rows)   

One other thing I noticed is that the conn_dboid is the same for two of the 
nodes. Is that normal?

-Selim


From: pgsql-general-ow...@postgresql.org [pgsql-general-ow...@postgresql.org] 
on behalf of Sylvain MARECHAL [marechal.sylva...@gmail.com]
Sent: Friday, December 04, 2015 10:14 AM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] BDR: ALTER statement hanging

Le 04/12/2015 18:59, Andreas Kretschmer a écrit :
>
>
> I think, the state 'i' is the main reason for your problem, because of: "i-
> Joining: The node is doing initial slot creation or an initial dump and load".
>
> But i can't tell you why this nodes are in this state.
>
>
> Regards, Andreas
>
>
Did-you check the bdr.bdr_connections table?
It should have as many lines as the bdr.bdr_nodes tables.

Sylvain


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] BDR: ALTER statement hanging

[Sylvain MARECHAL]

Le 04/12/2015 18:59, Andreas Kretschmer a écrit :



I think, the state 'i' is the main reason for your problem, because of: "i-
Joining: The node is doing initial slot creation or an initial dump and load".

But i can't tell you why this nodes are in this state.


Regards, Andreas



Did-you check the bdr.bdr_connections table?
It should have as many lines as the bdr.bdr_nodes tables.

Sylvain


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] BDR: ALTER statement hanging

[Andreas Kretschmer]

> Selim Tuvi  hat am 4. Dezember 2015 um 18:46 geschrieben:
> 
> 
> Yes they seem to be active:
> 
> deliver=# select * from pg_replication_slots;
> slot_name | plugin | slot_type | datoid |
> database | active | xmin | catalog_xmin | restart_lsn 
> --++---++--++--+--+-
>  bdr_533136_6223770712502831127_1_16389__ | bdr| logical   | 533136 |
> deliver  | t  |  |   182302 | 0/9C8A5598  
>  bdr_533136_6223800735012265413_1_16389__ | bdr| logical   | 533136 |
> deliver  | t  |  |   182302 | 0/9C8A5598  
> (2 rows) 
> 
> Although when I look at bdr.bdr_nodes I see the status as still initializing
> for the other two nodes, I don't know if that could cause this problem:
> 
> deliver=# select * from bdr.bdr_nodes;
>  node_sysid  | node_timeline | node_dboid | node_status |
>  node_name  |
> node_local_dsn
>  |
>  node_init_from_dsn
> -+---++-+-+---
> -+--
>  6212648563684174798 | 1 | 533136 | r   |
> pe-deliverdb-sf-01v | host=pe-deliverdb-sf-01v port=5432 dbname=deliver
> user=deliver_admin password=x   |
>  6223770712502831127 | 1 |  16389 | i   |
> pe-deliverdb-sing-01v | host=pe-deliverdb-sing-01v port=5432 dbname=deliver
> user=deliver_admin password=x | host=pe-deliverdb-sf-01v port=5432
> dbname=deliver user=deliver_admin password=x
>  6223800735012265413 | 1 |  16389 | i   |
> pe-deliverdb-lon-01v | host=pe-deliverdb-lon-01v port=5432 dbname=deliver
> user=deliver_admin password=x  | host=pe-deliverdb-sf-01v port=5432
> dbname=deliver user=deliver_admin password=x
> 
> -Selim
> 


I think, the state 'i' is the main reason for your problem, because of: "i-
Joining: The node is doing initial slot creation or an initial dump and load".

But i can't tell you why this nodes are in this state.


Regards, Andreas


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general