Re: [GENERAL] type "xxxxxxx" does not exist

[Adrian Klaver]

On 05/19/2017 01:57 PM, Micky Hulse wrote:

Wow, so many helpful replies already! Thanks everyone! I'm going to do
my best at answering questions … Starting from the first email reply.
:)




What version of Postgres, OS and how was it installed?


PostgreSQL 9.3.9 on i686-redhat-linux-gnu, compiled by gcc (GCC) 4.9.2
20150212 (Red Hat 4.9.2-6), 32-bit


FYI, 9.3 is now up to 9.3.17:

https://www.postgresql.org/docs/9.3/static/release.html

At some point, once you have gotten a handle on using Postgres, you 
should probably update. Read the Release Notes for each of the minor 
releases to see what has been fixed.




Installed via yum:

$ sudo yum install postgresql-server postgresql-contrib


I am asking because that will help find where pg_hba.conf is. If you have
found it, can you share it here?


Totally! pg_hba.conf lives here:

/var/lib/pgsql/data/pg_hba.conf

The only modifications I made was to change ident to md5 for IPv4 and
IPv6 local connections:

# TYPE  DATABASEUSERADDRESS METHOD
# "local" is for Unix domain socket connections only
local   all all peer
# IPv4 local connections:
hostall all 127.0.0.1/32md5
# IPv6 local connections:
hostall all ::1/128 md5
# Allow replication connections from localhost, by a user with the
# replication privilege.
#local   replication postgrespeer
#hostreplication postgres127.0.0.1/32ident
#hostreplication postgres::1/128 ident


So when you did this:

"
psql -U otherusername -d database

… I get:

psql: FATAL:  Peer authentication failed for user "otherusername"

"

you where connecting using local, which is the socket connection.

If you had done:

psql -U otherusername -d database -h localhost

it would have asked for a password(md5 auth method). If otherusername 
does not have the LOGIN attribute you would not been able to log in 
anyway. For more detailed information see:


https://www.postgresql.org/docs/9.3/static/sql-createrole.html







Great, thank you for the clarification!

Thank you for the help Adrian, I really appreciate it!





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


--
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] type "xxxxxxx" does not exist

[David G. Johnston]

On Fri, May 19, 2017 at 2:43 PM, Paul Jungwirth  wrote:

> On 05/19/2017 02:25 PM, Micky Hulse wrote:
>
>> Awesome, that worked!
>>
>> SET search_path TO myschema, public;
>>
>> Thanks to everyone for the help! I really appreciate it. :)
>>
>
> Glad you figured it out! Setting the seach_path is often a good thing to
> put in your ~/.psqlrc so you don't run into the same problem next time.


If going for out-of-sight, out-of-mind solutions, and I have superuser
access to the database, I'd much rather "ALTER DATABASE db SET search_path
TO 'all known schemas';"

The psqlrc file feels to disconnected for me.​

David J.

Re: [GENERAL] type "xxxxxxx" does not exist

[Paul Jungwirth]

On 05/19/2017 02:25 PM, Micky Hulse wrote:

Awesome, that worked!

SET search_path TO myschema, public;

Thanks to everyone for the help! I really appreciate it. :)


Glad you figured it out! Setting the seach_path is often a good thing to 
put in your ~/.psqlrc so you don't run into the same problem next time.


Paul




--
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] type "xxxxxxx" does not exist

[Micky Hulse]

On Fri, May 19, 2017 at 2:09 PM, Micky Hulse  wrote:
>> If you find that the type isn't in the public schema, try setting your
>> schema search path so that the function can locate it, e.g.:
>> SET search_path TO foo, public;

Awesome, that worked!

SET search_path TO myschema, public;

Thanks to everyone for the help! I really appreciate it. :)


-- 
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] type "xxxxxxx" does not exist

[Micky Hulse]

On Fri, May 19, 2017 at 2:09 PM, Micky Hulse  wrote:
> Cool! Dumb question, but is foo the schema or the type?

Doh! I see now that foo is the schema! Thanks again Paul!


-- 
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] type "xxxxxxx" does not exist

[Micky Hulse]

On Fri, May 19, 2017 at 1:31 PM, David G. Johnston
 wrote:
> Short answer here is that whomever is calling that function needs to ensure
> that their search_path is setup so that the type can be found somewhere in
> it.  Your desire for obscurity means you are pretty much on the hook for
> figuring out the right command to do so.

This is great information! I was not aware of the search_path.
Learning lots of new things here, so thank you David, and all, for the
help! I greatly appreciate it! :)

> See https://www.postgresql.org/docs/current/static/config-setting.html for
> help on various ways to go about making the actual change.

Will do, thanks!

>> I hope this is the right list for me to ask questions about psql.
>> Please let me know if I am in the wrong place. :)
> Right place

Cool! I much prefer listservs over something like StackOverflow as the
conversations tend to be more valuable to me (as has been proven in
this thread already). I'm happy to see that this list is active. :)

>> When listing the functions, I see that functionName() does exist in
>> the database.
> As the error is coming from within the function it indeed must exist and be
> visible to you.

Hehe, that's a good point! :D

>> The type also exists (I think):
> Existence and visability are two different things.  It indeed exists.  It is
> apparently not visible to the user when at the time the function is invoked
> - and the function doesn't explicitly say where to find it.

That makes so much sense now that you, and others, have pointed it
out. Thank you for kicking me in right direction!

>> Note that the role that owns the 'type' is not the same user that is
>> calling the "functionName()" from the psql prompt.
> Doesn't matter.  Types in PostgreSQL are not restricted since they never
> themselves contain any data.  As long as you can find a custom type you can
> use it.
>> ALTER TYPE xxx_xxx_x OWNER TO otherusername;
> All objects have owners.

Excellent information! Thank you so much for your help David! It's
greatly appreciated

Have a nice day!


-- 
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] type "xxxxxxx" does not exist

[Micky Hulse]

Hello and thanks for the help!

On Fri, May 19, 2017 at 1:25 PM, Paul Jungwirth
 wrote:
> It sounds like the type might be in a different schema. You can say \dn to
> see the schemas in your database, and \dT+ will show the types along with
> their schema. You could also do \dT+ foo.* to see all the types in schema
> foo.

A, interesting!

\dT+ myschema.*

I see the type "xxx_xxx_x" (which is the one my method is looking for).

There are not types in the public schema:

# \dT+ public.*
List of data types
 Schema | Name | Internal name | Size | Elements | Access privileges |
Description
+--+---+--+--+---+-
(0 rows)

> If you find that the type isn't in the public schema, try setting your
> schema search path so that the function can locate it, e.g.:
> SET search_path TO foo, public;

Cool! Dumb question, but is foo the schema or the type?

Thanks so much for the tips Paul! I really appreciate your help. :)


-- 
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] storing large files in database - performance

[Merlin Moncure]

On Fri, May 19, 2017 at 2:04 PM, Eric Hill  wrote:
> I am pleased to report that with Merlin's suggestion of using the 
> pg-large-object middleware, I have a test case now showing that I can write a 
> 25MB buffer from Node.js to Postgres in roughly 700 milliseconds.  Here is 
> the JavaScript code, which is nearly verbatim from the example in the 
> pg-large-object doc:
>
> packages.testLargeObjects = function(callback) {
>var pgp = require('pg-promise')();
>var LargeObjectManager = require('pg-large-object').LargeObjectManager;
>var PassThrough = require('stream').PassThrough;
>
>var bufSize = 1024 * 1024 * 25;
>var buf = new Buffer(bufSize);
>buf.fill("pgrocks");
>
>var connInfo = {
>   host:   'localhost',
>   port:   5432,
>   database:'mydb',
>   user:   'postgres,
>   password:'secret'
>};
>
>var db = pgp(connInfo);
>
>db.tx(function(tx) {
>   const lObjMgr = new LargeObjectManager({pgPromise: tx});
>   const bufferSize = 16384;
>
>   return lObjMgr.createAndWritableStreamAsync(bufferSize)
>  .then( ([oid, stream]) => {
> let bufferStream = new PassThrough();
> bufferStream.end(buf);
> bufferStream.pipe(stream);
> return new Promise(function(resolve, reject) {
>stream.on('finish', resolve);
>stream.on('error', reject);
> });
>  });
>})
>.then(function() {
>   callback();
>   pgp.end();
>})
>.catch(function(err) {
>   callback(err);
>   pgp.end();
>});
> };
>
> Thanks very much!

well done sir! that's probably as fast as you're going to get in node,
at least without a large investment at the driver level.

merlin


-- 
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] type "xxxxxxx" does not exist

[Micky Hulse]

Wow, so many helpful replies already! Thanks everyone! I'm going to do
my best at answering questions … Starting from the first email reply.
:)

On Fri, May 19, 2017 at 1:20 PM, Adrian Klaver
 wrote:
>> sudo -i -u username
> You should not need to do above.
>> psql -U otherusername -d database
> Just do the above.

Perfect, thanks for clarification.

> Are either username or otherusername a superuser?
> In psql \du will show you.

Great question.

username is a superuser and otherusername is not.

In fact, otherusername has no "local attributes" listed.

>> … I get:
>> psql: FATAL:  Peer authentication failed for user "otherusername"
> This is coming from:
> https://www.postgresql.org/docs/9.6/static/auth-methods.html#AUTH-PEER
> which is set in pg_hba.conf.

Ahh, thank you for tip!

> What version of Postgres, OS and how was it installed?

PostgreSQL 9.3.9 on i686-redhat-linux-gnu, compiled by gcc (GCC) 4.9.2
20150212 (Red Hat 4.9.2-6), 32-bit

Installed via yum:

$ sudo yum install postgresql-server postgresql-contrib

> I am asking because that will help find where pg_hba.conf is. If you have
> found it, can you share it here?

Totally! pg_hba.conf lives here:

/var/lib/pgsql/data/pg_hba.conf

The only modifications I made was to change ident to md5 for IPv4 and
IPv6 local connections:

# TYPE  DATABASEUSERADDRESS METHOD
# "local" is for Unix domain socket connections only
local   all all peer
# IPv4 local connections:
hostall all 127.0.0.1/32md5
# IPv6 local connections:
hostall all ::1/128 md5
# Allow replication connections from localhost, by a user with the
# replication privilege.
#local   replication postgrespeer
#hostreplication postgres127.0.0.1/32ident
#hostreplication postgres::1/128 ident

>> Do I need to create a Linux user to login as "otherusername" so I can
>> test calling the functionName() with xxx_xxx_x type?
> No that is not necessary. Postgres usernames do not have to be the same as
> the OS usernames. Peer authentication is just a method to map OS usernames
> to Postgres usernames if you want to.

Great, thank you for the clarification!

Thank you for the help Adrian, I really appreciate it!


-- 
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] type "xxxxxxx" does not exist

[David G. Johnston]

On Fri, May 19, 2017 at 1:06 PM, Micky Hulse  wrote:

​Short answer here is that whomever is calling that function needs to
ensure that their search_path is setup so that the type can be found
somewhere in it.  Your desire for obscurity means you are pretty much on
the hook for figuring out the right command to do so.

See https://www.postgresql.org/docs/current/static/config-setting.html for
help on various ways to go about making the actual change.


> I hope this is the right list for me to ask questions about psql.
> Please let me know if I am in the wrong place. :)
>

​Right place
​

>
> When listing the functions, I see that functionName() does exist in
> the database.
>

​As the error is coming from within the function it indeed must exist and
be visible to you.
​

>
> The type also exists (I think):
>
> # select exists (select 1 from pg_type where typname = 'xxx_xxx_x');
>  exists
> 
>  t
> (1 row)
>

​Existence and visability​ are two different things.  It indeed exists.  It
is apparently not visible to the user when at the time the function is
invoked - and the function doesn't explicitly say where to find it.


>
> Note that the role that owns the 'type' is not the same user that is
> calling the "functionName()" from the psql prompt.


Doesn't matter.  Types in PostgreSQL are not restricted since they never
themselves contain any data.  As long as you can find a custom type you can
use it.



> ALTER TYPE xxx_xxx_x OWNER TO otherusername;
>
>
​All objects have owners.​

​David J.

Re: [GENERAL] type "xxxxxxx" does not exist

[Paul Jungwirth]

On 05/19/2017 01:06 PM, Micky Hulse wrote:

ERROR:  type "xxx_xxx_x" does not exist
LINE 1:  DECLARE results xxx_xxx_x;


It sounds like the type might be in a different schema. You can say \dn 
to see the schemas in your database, and \dT+ will show the types along 
with their schema. You could also do \dT+ foo.* to see all the types in 
schema foo.


If you find that the type isn't in the public schema, try setting your 
schema search path so that the function can locate it, e.g.:


SET search_path TO foo, public;

Good luck!

Paul




--
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] type "xxxxxxx" does not exist

[Adrian Klaver]

On 05/19/2017 01:06 PM, Micky Hulse wrote:

Hello,

I hope this is the right list for me to ask questions about psql.
Please let me know if I am in the wrong place. :)

I am far from an advanced user of PostgreSQL, so please bear with me ...

I am working with an inherited database/codebase. I am trying to call
this function via psql:

# SELECT * FROM functionName('xxx', 'xxx', 'xxx');

What I get back is this:

ERROR:  type "xxx_xxx_x" does not exist
LINE 1:  DECLARE results xxx_xxx_x;
  ^
QUERY:   DECLARE results xxx_xxx_x;
.
.


When listing the functions, I see that functionName() does exist in
the database.

The type also exists (I think):

# select exists (select 1 from pg_type where typname = 'xxx_xxx_x');
  exists

  t
(1 row)

Note that the role that owns the 'type' is not the same user that is
calling the "functionName()" from the psql prompt. When I try to
switch roles, using:

sudo -i -u username


You should not need to do above.


psql -U otherusername -d database


Just do the above.

Are either username or otherusername a superuser?

In psql \du will show you.



… I get:

psql: FATAL:  Peer authentication failed for user "otherusername"


This is coming from:

https://www.postgresql.org/docs/9.6/static/auth-methods.html#AUTH-PEER

which is set in pg_hba.conf.

What version of Postgres, OS and how was it installed?

I am asking because that will help find where pg_hba.conf is. If you 
have found it, can you share it here?




Do I need to create a Linux user to login as "otherusername" so I can
test calling the functionName() with xxx_xxx_x type?


No that is not necessary. Postgres usernames do not have to be the same 
as the OS usernames. Peer authentication is just a method to map OS 
usernames to Postgres usernames if you want to.




Lastly, the type was declared in the SQL dump like this:

CREATE TYPE xxx_xxx_x AS (


);
ALTER TYPE xxx_xxx_x OWNER TO otherusername;


I know that's a lot of info ... More than anything, I'm just wondering
if someone can give me tips on where to focus my attention in terms of
trouble shooting?

Thanks so much!





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


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

[GENERAL] type "xxxxxxx" does not exist

[Micky Hulse]

Hello,

I hope this is the right list for me to ask questions about psql.
Please let me know if I am in the wrong place. :)

I am far from an advanced user of PostgreSQL, so please bear with me ...

I am working with an inherited database/codebase. I am trying to call
this function via psql:

# SELECT * FROM functionName('xxx', 'xxx', 'xxx');

What I get back is this:

ERROR:  type "xxx_xxx_x" does not exist
LINE 1:  DECLARE results xxx_xxx_x;
 ^
QUERY:   DECLARE results xxx_xxx_x;
.
.


When listing the functions, I see that functionName() does exist in
the database.

The type also exists (I think):

# select exists (select 1 from pg_type where typname = 'xxx_xxx_x');
 exists

 t
(1 row)

Note that the role that owns the 'type' is not the same user that is
calling the "functionName()" from the psql prompt. When I try to
switch roles, using:

sudo -i -u username
psql -U otherusername -d database

… I get:

psql: FATAL:  Peer authentication failed for user "otherusername"

Do I need to create a Linux user to login as "otherusername" so I can
test calling the functionName() with xxx_xxx_x type?

Lastly, the type was declared in the SQL dump like this:

CREATE TYPE xxx_xxx_x AS (


);
ALTER TYPE xxx_xxx_x OWNER TO otherusername;


I know that's a lot of info ... More than anything, I'm just wondering
if someone can give me tips on where to focus my attention in terms of
trouble shooting?

Thanks so much!


-- 
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] storing large files in database - performance

[Eric Hill]

I am pleased to report that with Merlin's suggestion of using the 
pg-large-object middleware, I have a test case now showing that I can write a 
25MB buffer from Node.js to Postgres in roughly 700 milliseconds.  Here is the 
JavaScript code, which is nearly verbatim from the example in the 
pg-large-object doc:

packages.testLargeObjects = function(callback) {
   var pgp = require('pg-promise')();
   var LargeObjectManager = require('pg-large-object').LargeObjectManager;
   var PassThrough = require('stream').PassThrough;

   var bufSize = 1024 * 1024 * 25;
   var buf = new Buffer(bufSize);
   buf.fill("pgrocks");

   var connInfo = {
  host:   'localhost',
  port:   5432,
  database:'mydb',
  user:   'postgres,
  password:'secret'
   };

   var db = pgp(connInfo);

   db.tx(function(tx) {
  const lObjMgr = new LargeObjectManager({pgPromise: tx});
  const bufferSize = 16384;

  return lObjMgr.createAndWritableStreamAsync(bufferSize)
 .then( ([oid, stream]) => {
let bufferStream = new PassThrough();
bufferStream.end(buf);
bufferStream.pipe(stream);
return new Promise(function(resolve, reject) {
   stream.on('finish', resolve);
   stream.on('error', reject);
});
 });
   })
   .then(function() {
  callback();
  pgp.end();
   })
   .catch(function(err) {
  callback(err);
  pgp.end();
   });
};

Thanks very much!

Eric

-Original Message-
From: Merlin Moncure [mailto:mmonc...@gmail.com] 

Another point, some googling turned up
https://www.npmjs.com/package/pg-large-object which is definitely something to 
consider trying.

merlin

-- 
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] Weird periodical pg log

[cen]

Hmm indeed that seems to be the case. Thanks for the tip!


On 05/19/2017 04:10 PM, John R Pierce wrote:

On 5/19/2017 1:25 AM, cen wrote:
< 2017-05-15 17:00:41.861 CEST >LOG:  parameter "archive_command" 
changed to ""/opt/omni/lbin/pgsqlbar.exe" -stage %p -backup"



I believe /opt/omni is the default installation path for HP Data 
Protector, formerly known as OmniBack.  That comprehensive backup 
system includes database backup capabilities, I'm guessing thats what 
you're seeing here.







--
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] Encrypt with BouncyCastle and decrypt with pg_pub_decrypt

[Jeff Janes]

On Thu, May 18, 2017 at 4:57 PM, Kang, Kamal 
wrote:

> Hi all,
>
>
>
> I am trying to encrypt a string using Bouncy Castle PGP Java apis, Base64
> encode the encrypted string and then decrypt using pg_pub_decrypt but it is
> failing with error “Wrong Key”. Just wanted to know if this is doable or
> pg_pub_decrypt only works with encrypted strings from pg_pub_encrypt?
>


pg_pub_decrypt is compatible with gpg, so if Bouncy Castle is also
compatible with gpg I don't see why it wouldn't also work.  Without more
information, it is hard to provide more advice.  encrypt a dummy payload
with a dummy password and show us what you get and what you do with it.

Cheers,

Jeff

Re: [GENERAL] Serializable isolation -- are predicate locks still held across all databases?

[Kevin Grittner]

On Fri, May 19, 2017 at 6:56 AM, Karl O. Pinc  wrote:

> I think if I was to make an argument for doing something it would
> be based on reliability -- how many users can you give their
> own database before somebody leaves an open transaction hanging?

Yeah, I guess it's worth having on the list, where it will compete
with other possible enhancements on a cost/benefit basis.  Thanks
for raising the issue!

--
Kevin Grittner
VMware vCenter Server
https://www.vmware.com/


-- 
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] Weird periodical pg log

[John R Pierce]

On 5/19/2017 1:25 AM, cen wrote:
< 2017-05-15 17:00:41.861 CEST >LOG:  parameter "archive_command" 
changed to ""/opt/omni/lbin/pgsqlbar.exe" -stage %p -backup"



I believe /opt/omni is the default installation path for HP Data 
Protector, formerly known as OmniBack.  That comprehensive backup 
system includes database backup capabilities, I'm guessing thats what 
you're seeing here.



--
john r pierce, recycling bits in santa cruz



--
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] Weird periodical pg log

[Adrian Klaver]

On 05/19/2017 01:25 AM, cen wrote:

Every single day at exactly the same hour I get this in my pg_log:


< 2017-05-15 17:00:29.517 CEST >FATAL:  pg_hba.conf rejects connection 
for host "...", user "postgres", database "template1", SSL off
< 2017-05-15 17:00:29.571 CEST >WARNING:  archive_mode enabled, yet 
archive_command is not set
< 2017-05-15 17:00:41.859 CEST >LOG:  received SIGHUP, reloading 
configuration files
< 2017-05-15 17:00:41.861 CEST >LOG:  parameter "archive_command" 
changed to ""/opt/omni/lbin/pgsqlbar.exe" -stage %p -backup"


Per Karsten's suggestion, the above seems to come from HP Enterprise 
software. As a start I would see what pgsqlbar.exe -help says.


< 2017-05-15 17:00:51.865 CEST >LOG:  received SIGHUP, reloading 
configuration files
< 2017-05-15 17:00:51.867 CEST >LOG:  parameter "archive_command" 
removed from configuration file, reset to default



Postgres 9.5 is running on Centos 7. I checked all cron jobs and 
scripts, there is nothing external that would cause this so my guess is 
that this is internal Postgres cron job tryin to do "something", I just 
don't know what exactly.


It is not an internal Postgres job. Something external to Postgres is 
making a change to postgresql.conf reloading the server conf files, 
undoing the change and then reloading the server conf files again.




We have WAL replication set up, these logs appear on master. Perhaps we 
have something off in our conf?



Best regards, cen






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


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

[GENERAL] Keeping sources of views, and tracking invalid objects (views) similar to oracle

[bb ddd]

Hello,

This idea has been raised more or less before as well as the problems it would 
solve. These are old, but IMHO the problem remains:

https://www.postgresql.org/message-id/24293.1272638299%40sss.pgh.pa.us
https://www.postgresql.org/message-id/D86CC5D8-C65A-4196-BB94-91614A814D29%40gtwm.co.uk
https://www.postgresql.org/message-id/4bd1c66e.6070...@comcast.net
https://www.postgresql.org/message-id/CAAQkdDod-N6nPbCKZ12zxjmYND%3D8tak3cZyJN40hELEbEfcw0A%40mail.gmail.com

But has been more or less discarded as "it's completely against the system 
structure at a number of levels" and "Oracle's approach is bad"

First of all I care mostly about views. These are extremely important and 
working with them in PG is a pain. We have several levels of view dependencies.
In addition to them being important in general, they are twice more important 
in PG where CTE's are optimization boundaries (for whatever reason) and if you 
want a decent plan and clean and easy to read short queries you just have to 
use views.

Want to make some points why I don't agree what has been previously said on 
this topic. 

Here is what I don't agree with (can be found in above links):
 
"That has some advantages; for example, you can rename a column in some other 
table that the view
uses, and nothing breaks" 
- Robert Haas

"IMO, the way Oracle does this pretty much sucks, and shouldn't be
emulated.  If they know how to recompile the view, why don't they
just do it?  What you describe is about as user-unfriendly as it
gets."
- Tom Lane

First of all how I look at this whole thing:

1. PG, not keeping the sources of the views, forces developers to maintain the 
sources externally (SVN, whatever). Not that it is a bad thing, but these 
sources are now logically a part of the database definition. You just need 
them. In oracle you are not forced to keep sources externally.
2. Given 1, In both PG and Oracle you HAVE invalid objects when you change the 
name of a table's column for example. In oracle these invalid objects are 
tracked, reported and recompiled if needed. In PG these invalid objects are the 
mentioned above sources that are kept externally.

To make 2. more clear. After say:

CREATE TABLE ttt(i NUMERIC);
INSERT INTO ttt (VALUES (1),(2),(3));
CREATE OR REPLACE VIEW v_ttt AS SELECT i FROM ttt;

SELECT * FROM v_ttt; -- returns 1,2,3

-- then :

ALTER TABLE ttt RENAME COLUMN i to s;
ALTER TABLE ttt add COLUMN i NUMERIC;


SELECT * FROM v_ttt; -- still returns 1,2,3

-- This according to Haas is a feature. In my book this is bad. Because the 
actual definition of the view is not what internally PG parsed and stored. The 
actual, important to the developers, definition of the view is stored 
externally in an .sql file in SVN
-- So next time a developer opens this file, fixes a bug in this view, or 
improves it or something. It will be recompiled and it will start using the new 
column and return null, null, null all of a sudden. So which was the expected 
behavior now???

About the second quote by Tom Lane:
If memory serves, they recompile the views, but not automatically. First time 
this view is about to be executed, if it is in an invalid state, the source 
code that is kept internally will be used to recompile it.
Which will propagate to recompiling all other invalid objects it depends on if 
any. If this is successful, all works fine. If not throws an error. Still the 
developer has an option, assuming he knows what he is doing, to not wait for 
this to happen, but ask the db for all invalid objects, and try recompile them 
himself.
Again this cannot happen in PG, because the actual definition of the same 
invalid objects (which actually in some cases keep working because of that 
"feature") live outside the database and pg has no idea how they look.

I understand this is one way to think of it. The PG way is that a table's 
column might change its name, but it is still the same column. But this is 
plain wrong. No database developer would ever think of it like this. 
And I don't think above scenario of renaming column and then adding a new one 
with the old name is uncommon.

This whole thing I wrote is just to get to my main point:
If view sources is preserved together with the parsed version (for performance 
reasons). You can:
* change view definition any way you can without having to drop all 100 
dependent views and recreating them again.
* This renaming thing will be more sane. Please if somebody actually ever 
relied on this "feature", prove me wrong. I cannot believe a developer would 
ever benefit from such behavior.
* For lazy people with simpler smaller database in one man projects, might not 
have to keep sources externally

Am I the only one that has problems with working with a lot of views? How do 
you solve these problems? Always drop and  recompile all views that depend on 
something? Granted with time it needs to happen less and less often because 
major changes after some point are not that needed. But 

Re: [GENERAL] Serializable isolation -- are predicate locks still held across all databases?

[Karl O. Pinc]

On Fri, 19 May 2017 01:52:00 -0500
"Karl O. Pinc"  wrote:

> On Thu, 18 May 2017 12:04:42 -0500
> Kevin Grittner  wrote:
> 
> > On Thu, May 18, 2017 at 11:07 AM, Karl O. Pinc  wrote:
> >   
> > > ...  Does PG
> > > now pay attention to database in it's SSI implementation?
> > 
> > Well, it pays attention as far as the scope of each lock, but there
> > is only one variable to track how far back the oldest transaction ID
> > for a running serializable transaction goes, which is used in
> > cleanup of old locks. 

> > ...  It's the
> > first time I've heard of someone with this particular issue, so at
> > this point I'm inclined to recommend the workaround of using a
> > separate cluster

I think if I was to make an argument for doing something it would
be based on reliability -- how many users can you give their
own database before somebody leaves an open transaction hanging?

Karl 
Free Software:  "You don't pay back, you pay forward."
 -- Robert A. Heinlein


-- 
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] Weird periodical pg log

[Karsten Hilbert]

On Fri, May 19, 2017 at 10:25:13AM +0200, cen wrote:

> < 2017-05-15 17:00:41.861 CEST >LOG:  parameter "archive_command" changed to 
> ""/opt/omni/lbin/pgsqlbar.exe" -stage %p -backup"

This is the line that you'll have to base your research on.

Also, you might want to check for a keylogger in the audio driver.

Regards,
Karsten
-- 
GPG key ID E4071346 @ eu.pool.sks-keyservers.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346


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

[GENERAL] Weird periodical pg log

[cen]

Every single day at exactly the same hour I get this in my pg_log:


< 2017-05-15 17:00:29.517 CEST >FATAL:  pg_hba.conf rejects connection 
for host "...", user "postgres", database "template1", SSL off
< 2017-05-15 17:00:29.571 CEST >WARNING:  archive_mode enabled, yet 
archive_command is not set
< 2017-05-15 17:00:41.859 CEST >LOG:  received SIGHUP, reloading 
configuration files
< 2017-05-15 17:00:41.861 CEST >LOG:  parameter "archive_command" 
changed to ""/opt/omni/lbin/pgsqlbar.exe" -stage %p -backup"
< 2017-05-15 17:00:51.865 CEST >LOG:  received SIGHUP, reloading 
configuration files
< 2017-05-15 17:00:51.867 CEST >LOG:  parameter "archive_command" 
removed from configuration file, reset to default



Postgres 9.5 is running on Centos 7. I checked all cron jobs and 
scripts, there is nothing external that would cause this so my guess is 
that this is internal Postgres cron job tryin to do "something", I just 
don't know what exactly.


We have WAL replication set up, these logs appear on master. Perhaps we 
have something off in our conf?



Best regards, cen



--
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] Error that shouldn't happen?

[vinny]

On 2017-05-18 21:48, Rob Brucks wrote:

Hello Everyone,

I am unable to figure out how the trigger was able to successfully
create the table, but then fail creating the index.  I would have
expected one thread to "win" and create both the table and index, but
other threads would fail when creating the table… but NOT when
creating the index.


First, I agree whole heartedly with the other's suggestions to "not do 
this".
Create a cronjob of whatever that prepares the required tables before 
you need them, empty tables are cheap.


Second: IF EXISTS only tells you that an object exists and is ready for 
use.
So what happens when a process is in the middle of creating that object? 
Does IF EXISTS tell you it exists or not?



What you need (accepting that this whole trigger based approach is 
probably not the best option)
is a proper locking mechanism. A "thundering herd" protection. The first 
time the trigger is triggered
it should set a lock (n advisory lock for example) that subsequent calls 
to the same trigger
can lok at to see if the table they need is being created at that time, 
so they will skip the create commands

and *WAIT* for the first process to complete before using the table.

That *WaIT* is important, and also something you probably don't want, 
especially if you have a busy database.



--
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] Serializable isolation -- are predicate locks still held across all databases?

[Karl O. Pinc]

On Thu, 18 May 2017 12:04:42 -0500
Kevin Grittner  wrote:

> On Thu, May 18, 2017 at 11:07 AM, Karl O. Pinc  wrote:
> 
> > ...  Does PG
> > now pay attention to database in it's SSI implementation?  
> 
> Well, it pays attention as far as the scope of each lock, but there
> is only one variable to track how far back the oldest transaction ID
> for a running serializable transaction goes, which is used in
> cleanup of old locks.  I see your point, and it might be feasible to
> change that to a list or map that tracks it by database; but I don't
> even have a gut feel estimate for the scale of such work without
> investigating it.  Just out of curiosity, what is the reason you
> don't move the production and test databases to separate instances?
> If nothing else, extremely long-running transaction in one database
> can lead to bloat in others.

Ultimately it was easier to change the transaction isolation level
to repeatable read (or lower) for the transactions known to take 
a long time.  Any concurrency issues (which have never arisen) 
are handled at the human level.

> > Thanks for the help and apologies if I'm not framing
> > the question perfectly.  It's not often I think about
> > this.  
> 
> No sweat -- your concern/question is perfectly clear.  It's the
> first time I've heard of someone with this particular issue, so at
> this point I'm inclined to recommend the workaround of using a
> separate cluster; but if we get other reports it might be worth
> adding to the list of enhancements that SSI could use.

Understood.

To give you an idea of the use-case, we're using Chado
(http://gmod.org/wiki/Chado) a PG database design
which stores genetic information.  The datasets being what
they are, they are big and take a long time to load.
This is especially true because the Chado designers
are enamored of ontologies and knowledge representation
and so there's a lot of tables where, instead of
having separate columns for different types of data
there's simply 2 columns "type" and "data".  The
type is an oncology entry and tells you want the
data is.  This makes for ugly queries in the process
of loading data (and ugly SQL in general).

So loading genetic data sets is slow.  Not really an issue
as there's no anticipation of loading a data set more
than every 6 months or a year.  (Although non-genetic
data is loaded frequently.)

The workflow is to load data first into the
test db, possibly multiple times until satisfied.
Then load the data into production.   It is very handy,
especially in production, to load all related data
in a single transaction in the event something
goes wrong.

There are many non-optimal elements, not the least
of which is that it's not clear how much utility
there is in storing genetic datasets
in a relational db along side our non-genetic data.
(We are finding out.)

Thanks for the help.

Karl 
Free Software:  "You don't pay back, you pay forward."
 -- Robert A. Heinlein


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