SQL questiom

[hamann . w]

Hi,

I am using a query pretty often that looks like
SELECT <> WHERE <> AND
<>

Is there a way (with sql or plpgsql)  to convert that into
SELECT myquery('<>')

Kind regards
Wolfgang Hamann

Re: SQL questiom

[Thomas Boussekey]

Le ven. 21 janv. 2022 à 11:14,  a écrit :

>
>
> Hi,
>
> I am using a query pretty often that looks like
> SELECT <> WHERE <> AND
> <>
>
> Is there a way (with sql or plpgsql)  to convert that into
> SELECT myquery('<>')
>
> Kind regards
> Wolfgang Hamann
>
>
>
> Hello Wolfgang,

You can use a FUNCTION
 to
perform this operation

HTH,
Thomas

PgAdmin is struggling and can we configure it so that it works better

[Shaozhong SHI]

Some time, PgAdmin freezes and its response is very slow.

Some time, it gives strange display of table content.  Perhaps, it is
related to the fact that PgAdmin is struggling with the amount of data it
thinks that it has to display.

I just wonder whether we can configure PgAdmin so that it will work better.

Regards,

David

Re: Query on postgres_fdw extension

[Duarte Carreira]

Thanks for your help!

I'm not going forward with the id generating scheme... I prefer to let the
bd do that work on its own. Sharding is way over my head.
For now I just created the 2 tables, one for inserting (without the id
column), another for everything else. It's awkward and prone to human error
but as long as nothing changes and no one deletes it thinking it's
garbage...

Thanks.

Vijaykumar Jain  escreveu no dia quinta,
20/01/2022 à(s) 17:39:

> On Thu, 20 Jan 2022 at 21:29, Duarte Carreira 
> wrote:
>
>> Hello everyone.
>>
>> I don't know... realistically what do you guys see as a best/simple
>> approach?
>>
>
> We implemented a custom sharding (directory sharding with lookup tables)
> layer of 10 shards, but it was write local, read global.
> the api was responsible for all rebalancing incase of hotspots.
> other api sharding examples ...
> Database Sharding: Solving Performance in a  Multi-Tenant Restaurant Data
> Analytics System (gotenzo.com)
> 
>
>
> 
>  although
> it worked really well, when you are maintaining it on your own, it gets
> really painful, much beyond id generation globally.
>
> i will not go into the details, but in short, sharded setup is not the
> same as local setup. there would be many more things that would not work as
> expected
> which would otherwise work really well on a standalone setup.
>
> writes over shard may work, but you realize it is over the network, so you
> can lock you table for a much longer duration and cause a much more serious
> outage,
> if you really wanted to have distributed writes with unique keys, you can
> go with uuid i think or have your own seq generator globally (see below).
>
>
> *Move ID generation out of the database to an ID generation service
> outside of the database… As soon as a piece of work enters their system, an
> ID gets assigned to it… and that ID generated in a way that is known to be
> globally unique within their system*
>
> A Better ID Generator For PostgreSQL | robconery
> 
> Index of /shard_manager/shard_manager-0.0.1/ (pgxn.org)
>   (pretty
> old but if you can use your coordinator server as a id_generator(), then
> you can generate ids which are globally unique)
> Sharding & IDs at Instagram. With more than 25 photos and 90 likes… | by
> Instagram Engineering | Instagram Engineering (instagram-engineering.com)
> 
>
> imho, do not try sharding manually, unless you have enough dbas to
> maintain the shards, try using citus, it would make a lot of the manual
> stuff easier.
>
> also, the below work arounds are bad, incase you just want to rush through
>
> postgres=# \c localdb
> You are now connected to database "localdb" as user "postgres".
> localdb=#
> localdb=# \dt
> Did not find any relations.
> localdb=# \det
>  List of foreign tables
>  Schema | Table |Server
> +---+---
>  public | t | remote_server
> (1 row)
>
> localdb=# \det+ t
> List of foreign tables
>  Schema | Table |Server |  FDW options   |
> Description
>
> +---+---++-
>  public | t | remote_server | (schema_name 'public', table_name 't') |
> (1 row)
>
> localdb=# \det t
>  List of foreign tables
>  Schema | Table |Server
> +---+---
>  public | t | remote_server
> (1 row)
>
> localdb=# create or replace function getnext() returns int as $_$ select
> id FROM dblink ('dbname = remotedb', $$ select nextval('t_id_seq') $$  )
> as  newtable(id int); $_$ language sql;
> CREATE FUNCTION
> localdb=# \c remotedb
> You are now connected to database "remotedb" as user "postgres".
> remotedb=# \dt t
> List of relations
>  Schema | Name | Type  |  Owner
> +--+---+--
>  public | t| table | postgres
> (1 row)
>
> remotedb=# \ds t_id_seq
> List of relations
>  Schema |   Name   |   Type   |  Owner
> +--+--+--
>  public | t_id_seq | sequence | postgres
> (1 row)
>
> remotedb=# \c localdb
> You are now connected to database "localdb" as user "postgres".
> localdb=# insert into t values (getnext(), 100);
> INSERT 0 1
> localdb=# insert into t values (getnext(), 100);
> INSERT 0 1
> localdb=# select * from t;
>  id | col1
> +--
>  11 |4
>  12 |5
>  13 |  100
>  14 |  100
> (4 rows)
>
> just my opinion, ignore it not useful.
>
>
>

Re: SQL questiom

[Jacob Bunk Nielsen]

haman...@t-online.de writes:

> I am using a query pretty often that looks like
> SELECT <> WHERE <> AND
> <>
>
> Is there a way (with sql or plpgsql)  to convert that into
> SELECT myquery('<>')

I would solve that by creating a view like:

CREATE VIEW some_view_name AS 
  SELECT <> WHERE <>;

See also https://www.postgresql.org/docs/14/sql-createview.html

Then you can query that view with your actual select criterion like:

SELECT * FROM some_view_name WHERE <>;

Best regards,
Jacob

recording of INDEX creation in tables

[Matthias Apitz]

Hello,

Does the PostgreSQL (11.4 or 13.1) record somewhere in system tables
the creation of INDEXes (or other objects)?

Thanks

matthias
-- 
Matthias Apitz, ✉ g...@unixarea.de, http://www.unixarea.de/ +49-176-38902045
Public GnuPG key: http://www.unixarea.de/key.pub
August 13, 1961: Better a wall than a war. And, while the GDR was still 
existing,
no German troups and bombs have been killed in Yugoslavia, Afghanistan, 
Afrika...

Fwd: PgAdmin is struggling and can we configure it so that it works better

[Tomas Pospisek]

Forgot to include pgsql-general@lists.postgresql.org in the Cc...


 Forwarded Message 
Subject: Re: PgAdmin is struggling and can we configure it so that it 
works better

Date: Fri, 21 Jan 2022 13:42:13 +0100
From: Tomas Pospisek 
To: Shaozhong SHI 

On 21.01.22 12:42, Shaozhong SHI wrote:

Some time, PgAdmin freezes and its response is very slow.

Some time, it gives strange display of table content.  Perhaps, it is 
related to the fact that PgAdmin is struggling with the amount of data 
it thinks that it has to display.


I just wonder whether we can configure PgAdmin so that it will work better.


You can compare the times the same query takes to display the result 
locally via PgAdmin and remotely, when you execute the same query on 
that server with psql. That will give you an idea on whether the long 
execution time is on the server or not.

*t

proj_create errors in EDB 13.5 installation macOS

[Moen, Paul T.]

I am seeing the following errors in my PostgreSQL log files and wonder if this 
is a problem with EDB's installation.

proj_create: Cannot find proj.db
proj_create: no database context specified
proj_create: Cannot find proj.db
proj_create: no database context specified
proj_create: Cannot find proj.db
proj_create: no database context specified
proj_create: Cannot find proj.db
proj_create: no database context specified
proj_create: Cannot find proj.db
proj_create_operation_factory_context: Cannot find proj.db
pj_obj_create: Cannot find proj.db
proj_create_operation_factory_context: Cannot find proj.db
pj_obj_create: Cannot find proj.db
proj_create_operation_factory_context: Cannot find proj.db
pj_obj_create: Cannot find proj.db
proj_create_operation_factory_context: Cannot find proj.db
pj_obj_create: Cannot find proj.db
proj_create_operation_factory_context: Cannot find proj.db
pj_obj_create: Cannot find proj.db

I used the EDB installer for 13.5 from 
www.enterprisedb.com/downloads/postgres-postgresql-downloads

macOS Monterey 12.1 Darwin 21.2.0 Darwin Kernel Version 21.2.0: Sun Nov 28 
20:28:54 PST 2021; root:xnu-8019.61.5~1/RELEASE_X86_64 x86_64
psql (PostgreSQL) 13.5
POSTGIS="3.0.2 2fb2a18" [EXTENSION] PGSQL="130" GEOS="3.8.1-CAPI-1.13.3" 
PROJ="7.1.1" LIBXML="2.9.10" LIBJSON="0.15"


Any suggestions?

Paul

Can commands be typed in to view geometry in PgAdmin?

[Shaozhong SHI]

I just wonder whether commands can be typed in PgAdmin to view geometries.

Regards,

David

Re: Query on postgres_fdw extension

[Laurenz Albe]

On Thu, 2022-01-20 at 15:59 +, Duarte Carreira wrote:
> I got here after encountering the same difficulty, although on a much more 
> mundane scenario.
> 
> I'm used to fdw on a read-only basis. I was just inserting a new record on a 
> foreign table
> and got blocked... and after much searching got here.
> 
> As far as I can see it is impossible to use fdw to insert records on 99% of 
> tables,
> since all have some kind of primary sequential key.

Yes, this is tricky.  You could use something like this:

CREATE TABLE local (
   id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
   data text
);

CREATE FOREIGN TABLE remote (id bigint NOT NULL, data text)
   SERVER whatever OPTIONS (table_name 'local');

CREATE FOREIGN TABLE remote_noid (data text)
   SERVER whatever OPTIONS (table_name 'local');

CREATE VIEW v_remote AS SELECT * FROM remote;

CREATE FUNCTION ins_trig() RETURNS trigger LANGUAGE plpgsql AS
$$BEGIN
   INSERT INTO remote_noid (data) VALUES (NEW.data);
   RETURN NEW;
END;$$;

CREATE TRIGGER ins_trig INSTEAD OF INSERT ON v_remote
FOR EACH ROW EXECUTE FUNCTION ins_trig();

INSERT INTO v_remote (data) VALUES ('something');

SELECT * FROM v_remote;

 id │   data
╪═══
  1 │ something

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com

Re: [EXT] Re: Can we get the CTID value

[Laurenz Albe]

On Thu, 2022-01-20 at 17:00 +, Garfield Lewis wrote:
> I need the page and possibly row of the data location to be stored as an 
> element
> of the new type. This is to simulate a structure from another database system.

As I said, that is impossible.

Again, describe with many, many words what you are trying to achieve.
There is probably a solution for the underlying problem.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com

Re: Query on postgres_fdw extension

[Duarte Carreira]

Hmmm... I don't think a view or trigger are necessary.

If we just create the 2 foreign tables, one complete and one without id,
you can simply insert into the table without id and it will work fine.
To select and show data, you use the "complete" table that has the id
column.

No need for trigger and view. If I understood correctly.

I have this 2 table setup working.

It's a workaround that quickly escalates out of hand though... with little
added value.

Thanks.

Laurenz Albe  escreveu no dia sexta, 21/01/2022
à(s) 13:59:

> On Thu, 2022-01-20 at 15:59 +, Duarte Carreira wrote:
> > I got here after encountering the same difficulty, although on a much
> more mundane scenario.
> >
> > I'm used to fdw on a read-only basis. I was just inserting a new record
> on a foreign table
> > and got blocked... and after much searching got here.
> >
> > As far as I can see it is impossible to use fdw to insert records on 99%
> of tables,
> > since all have some kind of primary sequential key.
>
> Yes, this is tricky.  You could use something like this:
>
> CREATE TABLE local (
>id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
>data text
> );
>
> CREATE FOREIGN TABLE remote (id bigint NOT NULL, data text)
>SERVER whatever OPTIONS (table_name 'local');
>
> CREATE FOREIGN TABLE remote_noid (data text)
>SERVER whatever OPTIONS (table_name 'local');
>
> CREATE VIEW v_remote AS SELECT * FROM remote;
>
> CREATE FUNCTION ins_trig() RETURNS trigger LANGUAGE plpgsql AS
> $$BEGIN
>INSERT INTO remote_noid (data) VALUES (NEW.data);
>RETURN NEW;
> END;$$;
>
> CREATE TRIGGER ins_trig INSTEAD OF INSERT ON v_remote
> FOR EACH ROW EXECUTE FUNCTION ins_trig();
>
> INSERT INTO v_remote (data) VALUES ('something');
>
> SELECT * FROM v_remote;
>
>  id │   data
> ╪═══
>   1 │ something
>
> Yours,
> Laurenz Albe
> --
> Cybertec | https://www.cybertec-postgresql.com
>
>

Re: Can commands be typed in to view geometry in PgAdmin?

[David G. Johnston]

It is amazing how much one can learn by reading documentation.  You are
looking for a section that describes how to view, and possibly edit, data
in pgAdmin4.

David J.


On Fri, Jan 21, 2022 at 5:52 AM Shaozhong SHI 
wrote:

> I just wonder whether commands can be typed in PgAdmin to view geometries.
>
> Regards,
>
> David
>

Re: proj_create errors in EDB 13.5 installation macOS

[Paul Ramsey]

> On Jan 21, 2022, at 4:45 AM, Moen, Paul T.  wrote:
> 
> I am seeing the following errors in my PostgreSQL log files and wonder if 
> this is a problem with EDB's installation. 

Yes, missing the proj.db would certainly make proj unhappy. Search your system 
first and see if the file is actually there. Maybe it's there but not being 
found, in which case some extra environment variables could make your install 
turn over. But EBD perhaps needs to make sure that it's both (a) included and 
(b) found on startup.

P

Re: Query on postgres_fdw extension

[Laurenz Albe]

On Fri, 2022-01-21 at 14:33 +, Duarte Carreira wrote:
> If we just create the 2 foreign tables, one complete and one without id,
> you can simply insert into the table without id and it will work fine.
> To select and show data, you use the "complete" table that has the id column.
> 
> No need for trigger and view. If I understood correctly.
> 
> I have this 2 table setup working.
> 
> It's a workaround that quickly escalates out of hand though... with little 
> added value.

Sure, that will work.

I just wanted to show how you can do without dealing with two different
foreign tables explicitly.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com

Using a different column name in a foreign table

[Alanoly Andrews]

Hello,

I see that the syntax for the creation of a foreign table allows you to use a 
column name in the FT that is different from the one in the base table. Such a 
"create foreign table" statement executes successfully and creates the FT. But 
when I query the FT, I get an error wrt to the column that had been renamed. 
See example below:

create foreign table tab1_ft (
id int,
name char(10) options(column_name 'newname'))
server xx
options(schema_name 'public', table_name 'tab1');

select * from tab1_ft;

ERROR:  column "newname" does not exist
HINT:  Perhaps you meant to reference the column "tab1.name".
CONTEXT:  Remote SQL command: SELECT id, newname FROM public.tab1

So, it seems that the when the remote SQL command is composed, the mapping of 
'newname' to the 'name' in the base table does not take effect.
Is there a resolution to this issue?

Awaiting some input.

Alanoly Andrews.




This e-mail may be privileged and/or confidential, and the sender does not 
waive any related rights and obligations. Any distribution, use or copying of 
this e-mail or the information it contains by other than an intended recipient 
is unauthorized. If you received this e-mail in error, please advise me (by 
return e-mail or otherwise) immediately.

Ce courriel est confidentiel et prot?g?. L'exp?diteur ne renonce pas aux droits 
et obligations qui s'y rapportent. Toute diffusion, utilisation ou copie de ce 
message ou des renseignements qu'il contient par une personne autre que le 
(les) destinataire(s) d?sign?(s) est interdite. Si vous recevez ce courriel par 
erreur, veuillez m'en aviser imm?diatement, par retour de courriel ou par un 
autre moyen.'. If the disclaimer can't be applied, attach the message to a new 
disclaimer message.

Re: Can commands be typed in to view geometry in PgAdmin?

[Shaozhong SHI]

It would be nice to be able to write lines of scripts to tell PgAdmin to
show some visualisation of geographical features.

Regards,

David

On Fri, 21 Jan 2022 at 15:14, David G. Johnston 
wrote:

> It is amazing how much one can learn by reading documentation.  You are
> looking for a section that describes how to view, and possibly edit, data
> in pgAdmin4.
>
> David J.
>
>
> On Fri, Jan 21, 2022 at 5:52 AM Shaozhong SHI 
> wrote:
>
>> I just wonder whether commands can be typed in PgAdmin to view geometries.
>>
>> Regards,
>>
>> David
>>
>

Re: Query on postgres_fdw extension

[Shaozhong SHI]

Any functional code to be tested to confirm?

Regards,

David

On Fri, 21 Jan 2022 at 15:55, Laurenz Albe  wrote:

> On Fri, 2022-01-21 at 14:33 +, Duarte Carreira wrote:
> > If we just create the 2 foreign tables, one complete and one without id,
> > you can simply insert into the table without id and it will work fine.
> > To select and show data, you use the "complete" table that has the id
> column.
> >
> > No need for trigger and view. If I understood correctly.
> >
> > I have this 2 table setup working.
> >
> > It's a workaround that quickly escalates out of hand though... with
> little added value.
>
> Sure, that will work.
>
> I just wanted to show how you can do without dealing with two different
> foreign tables explicitly.
>
> Yours,
> Laurenz Albe
> --
> Cybertec | https://www.cybertec-postgresql.com
>
>
>
>

Re: Using a different column name in a foreign table

[Adrian Klaver]

On 1/21/22 08:24, Alanoly Andrews wrote:

Hello,
I see that the syntax for the creation of a foreign table allows you to 
use a column name in the FT that is different from the one in the base 
table. Such a "create foreign table" statement executes successfully and 
creates the FT. But when I query the FT, I get an error wrt to the 
column that had been renamed. See example below:

create foreign table tab1_ft (
   id int,
     name char(10) options(column_name 'newname'))
server xx
options(schema_name 'public', table_name 'tab1');


What is the definition of the table tab1?


select * from tab1_ft;
ERROR:  column "newname" does not exist
HINT:  Perhaps you meant to reference the column "tab1.name".
CONTEXT:  Remote SQL command: SELECT id, newname FROM public.tab1
So, it seems that the when the remote SQL command is composed, the 
mapping of 'newname' to the 'name' in the base table does not take effect.

Is there a resolution to this issue?
Awaiting some input.
Alanoly Andrews.





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

Does PostgreSQL do bind-peeking? Is `col like '%'` optimized-away by the planner?

[Dominique Devienne]

Hi,

I just saw some code of ours that takes 4 strings are arguments,
and wants to do optional filtering on those, in a SELECT statement.
Something like:
```
void foo(string arg1, string arg2, ...) {
... = exec(
conn, "SELECT * from tab where col1 like $1 and col2 like $2 and ...",
arg1.empty()? "%": arg1, arg2.empty()? "%": arg2, ...
);
}
```
where the exec() helper does proper binding of the argN strings.

Will the query planner be able to *peek* into the args, and turn the
`colN like $N`
into a no-op? Note that in this case, this is *not* a prepared
statement at the moment,
but it could be in the future.

So I guess my question can also be viewed as whether it's worth
preparing several statements
for the various cases of empty argN strings, or does the planner do
*bind-peeking*, and thus a single prepared statement would do the job,
and still have different plans used depending on the actual binds?

I'm assuming PostgreSQL does bind-peeking like Oracle, but I don't
know, and I've never read anything yet about that.

Thanks, --DD

Re: Does PostgreSQL do bind-peeking? Is `col like '%'` optimized-away by the planner?

[David G. Johnston]

On Fri, Jan 21, 2022 at 9:36 AM Dominique Devienne 
wrote:

>
> for the various cases of empty argN strings, or does the planner do
> *bind-peeking*, and thus a single prepared statement would do the job,
> and still have different plans used depending on the actual binds?
>
> I'm assuming PostgreSQL does bind-peeking like Oracle, but I don't
> know, and I've never read anything yet about that.
>
>
A prepared statement either generates a custom plan and, as a side-effect,
does bind-peeking, or it uses the single prepared plan it has established
and executes that.  It doesn't use bind-peeking to decide among multiple
saved prepared plans. There is more to it than that, like a 5 custom plan
threshold before abandoning bind-peeking (I think I got that right...).

David J.

Re: Using a different column name in a foreign table

[Guillaume Lelarge]

Hi,

Le ven. 21 janv. 2022 à 17:24, Alanoly Andrews  a
écrit :

> Hello,
>
> I see that the syntax for the creation of a foreign table allows you to
> use a column name in the FT that is different from the one in the base
> table. Such a "create foreign table" statement executes successfully and
> creates the FT. But when I query the FT, I get an error wrt to the column
> that had been renamed. See example below:
>
> create foreign table tab1_ft (
>   id int,
> name char(10) options(column_name 'newname'))
> server xx
> options(schema_name 'public', table_name 'tab1');
>
> select * from tab1_ft;
>
> ERROR:  column "newname" does not exist
> HINT:  Perhaps you meant to reference the column "tab1.name".
> CONTEXT:  Remote SQL command: SELECT id, newname FROM public.tab1
>
> So, it seems that the when the remote SQL command is composed, the mapping
> of 'newname' to the 'name' in the base table does not take effect.
> Is there a resolution to this issue?
>
>

Your foreign table definition should have the new column name. You did it
the other way around. This is how you should have done it:

create foreign table tab1_ft (
  id int,
  newname char(10) options(column_name 'name'))
server xx
options(schema_name 'public', table_name 'tab1');

Regards.


-- 
Guillaume.

Re: Does PostgreSQL do bind-peeking? Is `col like '%'` optimized-away by the planner?

[Tom Lane]

Dominique Devienne  writes:
> Will the query planner be able to *peek* into the args, and turn the
> `colN like $N`
> into a no-op?

No.  It would not do that even if the pattern were constant '%';
it doesn't know that much about that particular function.

There is a notion of "custom plans" in which parameter values are
inserted as constants, precisely to allow simplifications based on
known constant values.  But this particular case isn't implemented.

I am entirely unfamiliar with the terminology "bind-peeking", so
I can't say whether that's effectively the same thing as our
custom plans.

regards, tom lane

Re: Can commands be typed in to view geometry in PgAdmin?

[David G. Johnston]

On Fri, Jan 21, 2022 at 9:26 AM Shaozhong SHI 
wrote:

> It would be nice to be able to write lines of scripts to tell PgAdmin to
> show some visualisation of geographical features.
>

I wasn't aware pgAdmin had a command line oriented interface.  I thought it
was pure GUI.  The only scripts you write are SQL scripts executed on the
server - pgAdmin is just the messenger.  Though it does provide GUI
elements to view those results that it receives.  Both the result tables
themselves and the column contents of various data types.

David J.

Re: Using a different column name in a foreign table

[Tom Lane]

Alanoly Andrews  writes:
> I see that the syntax for the creation of a foreign table allows you to use a 
> column name in the FT that is different from the one in the base table. Such 
> a "create foreign table" statement executes successfully and creates the FT. 
> But when I query the FT, I get an error wrt to the column that had been 
> renamed. See example below:

> create foreign table tab1_ft (
> id int,
> name char(10) options(column_name 'newname'))
> server xx
> options(schema_name 'public', table_name 'tab1');

> select * from tab1_ft;

> ERROR:  column "newname" does not exist
> HINT:  Perhaps you meant to reference the column "tab1.name".
> CONTEXT:  Remote SQL command: SELECT id, newname FROM public.tab1

> So, it seems that the when the remote SQL command is composed, the mapping of 
> 'newname' to the 'name' in the base table does not take effect.

Huh?  The CONTEXT line shows what was issued to the remote server,
and it's very obvious that we *are* asking for "newname", as indeed
is also implied by the error issued by the remote.  I think you
just didn't match the name correctly to the actual name on the remote.

regards, tom lane

RE: Using a different column name in a foreign table

[Alanoly Andrews]

Thanks Tom, Guillaume and Adrian.
My mistake in reversing the order of name and newname in the definition.
After the switch, it works.

ButI'm investigating another environment where the definition appears to 
have been correct and yet there is an error in select. Maybe I'll post again 
later, if needed.

Thanks.

A.A.

-Original Message-
From: Tom Lane [mailto:t...@sss.pgh.pa.us]
Sent: Friday, January 21, 2022 11:52 AM
To: Alanoly Andrews 
Cc: pgsql-general@lists.postgresql.org
Subject: Re: Using a different column name in a foreign table

[Email External/Externe] Caution opening links or attachments/attention lors de 
l'ouverture de liens ou de pièces jointes.

Alanoly Andrews  writes:
> I see that the syntax for the creation of a foreign table allows you to use a 
> column name in the FT that is different from the one in the base table. Such 
> a "create foreign table" statement executes successfully and creates the FT. 
> But when I query the FT, I get an error wrt to the column that had been 
> renamed. See example below:

> create foreign table tab1_ft (
> id int,
> name char(10) options(column_name 'newname')) server xx
> options(schema_name 'public', table_name 'tab1');

> select * from tab1_ft;

> ERROR:  column "newname" does not exist
> HINT:  Perhaps you meant to reference the column "tab1.name".
> CONTEXT:  Remote SQL command: SELECT id, newname FROM public.tab1

> So, it seems that the when the remote SQL command is composed, the mapping of 
> 'newname' to the 'name' in the base table does not take effect.

Huh?  The CONTEXT line shows what was issued to the remote server, and it's 
very obvious that we *are* asking for "newname", as indeed is also implied by 
the error issued by the remote.  I think you just didn't match the name 
correctly to the actual name on the remote.

regards, tom lane

This e-mail may be privileged and/or confidential, and the sender does not 
waive any related rights and obligations. Any distribution, use or copying of 
this e-mail or the information it contains by other than an intended recipient 
is unauthorized. If you received this e-mail in error, please advise me (by 
return e-mail or otherwise) immediately.


Ce courriel est confidentiel et protégé. L'expéditeur ne renonce pas aux droits 
et obligations qui s'y rapportent. Toute diffusion, utilisation ou copie de ce 
message ou des renseignements qu'il contient par une personne autre que le 
(les) destinataire(s) désigné(s) est interdite. Si vous recevez ce courriel par 
erreur, veuillez m'en aviser immédiatement, par retour de courriel ou par un 
autre moyen.'. If the disclaimer can't be applied, attach the message to a new 
disclaimer message.

Re: Does PostgreSQL do bind-peeking? Is `col like '%'` optimized-away by the planner?

[Dominique Devienne]

On Fri, Jan 21, 2022 at 5:47 PM Tom Lane  wrote:
>> Dominique Devienne  writes:
> > Will the query planner be able to *peek* into the args, and turn `colN like 
> > $N` into a no-op?

Thanks for the replies, David and Tom.

> No.  It would not do that even if the pattern were constant '%';
> it doesn't know that much about that particular function.

Interesting. Thanks.

> There is a notion of "custom plans" in which parameter values are
> inserted as constants, precisely to allow simplifications based on
> known constant values.  But this particular case isn't implemented.

Where can I read more about this? And is it something the client has
any influence on?

> I am entirely unfamiliar with the terminology "bind-peeking"

I thought it was "standard speak" in the SQL world :)
I don't recall where I picked that up, to be honest.

Here's what seems like an official use in the Oracle PL/SQL doc:
https://oracle.readthedocs.io/en/latest/plsql/bind/bind-peeking.html

But that link does imply that what I thought could happen in Oracle,
i.e. that it could keep several plans for the same prepared statement,
that it would select at runtime based on the actual binds, is NOT
happening in Oracle either...

Thanks, --DD

Re: Does PostgreSQL do bind-peeking? Is `col like '%'` optimized-away by the planner?

[Tom Lane]

Dominique Devienne  writes:
> On Fri, Jan 21, 2022 at 5:47 PM Tom Lane  wrote:
>> There is a notion of "custom plans" in which parameter values are
>> inserted as constants, precisely to allow simplifications based on
>> known constant values.  But this particular case isn't implemented.

> Where can I read more about this? And is it something the client has
> any influence on?

Start here:

https://www.postgresql.org/docs/current/sql-prepare.html

https://www.postgresql.org/docs/current/plpgsql-implementation.html#PLPGSQL-PLAN-CACHING

regards, tom lane

RE: Using a different column name in a foreign table

[Alanoly Andrews]

Following up on my previous message, there was a related problem with FT 
definitions for which I have a workaround. It seems that the new column_name 
after "options" in an FT definition cannot be in upper case. See below:

When I define a foreign table like below, I get an error in the subsequent 
select statement:

create foreign table scvmgl_rec (
MGL_CLNT_HOST_NM char(15) options (column_name 'MSG_CLNT_HOST_NM'))
server pglpolaris12018
options (schema_name 'informix', table_name 'sctmsg_rec');

ai01pr00=# select * from scvmgl_rec;
ERROR:  column "MSG_CLNT_HOST_NM" does not exist
CONTEXT:  Remote SQL command: SELECT "MSG_CLNT_HOST_NM" FROM informix.sctmsg_rec

But when I define the FT as below, there is no error in the subsequent "select".

create foreign table scvmgl_rec (
mgl_clnt_host_nm char(15) options (column_name 'msg_clnt_host_nm'))
server pglpolaris12018
options (schema_name 'informix', table_name 'sctmsg_rec');

So, apparently postgres transmits the remote query with double quotes around 
the upper-case column name; and such a query comes back with "column does not 
exist". But a double quote around a lower-case column name does not produce the 
error.

Is this a bug that needs to be fixed? I always thought that table names and 
column names were case-insensitive with regard to SQL's.

A.A.

-Original Message-
From: Alanoly Andrews
Sent: Friday, January 21, 2022 12:03 PM
To: Tom Lane ; Guillaume Lelarge ; 
Adrian Klaver 
Cc: pgsql-general@lists.postgresql.org
Subject: RE: Using a different column name in a foreign table

Thanks Tom, Guillaume and Adrian.
My mistake in reversing the order of name and newname in the definition.
After the switch, it works.

ButI'm investigating another environment where the definition appears to 
have been correct and yet there is an error in select. Maybe I'll post again 
later, if needed.

Thanks.

A.A.

-Original Message-
From: Tom Lane [mailto:t...@sss.pgh.pa.us]
Sent: Friday, January 21, 2022 11:52 AM
To: Alanoly Andrews mailto:alano...@invera.com>>
Cc: 
pgsql-general@lists.postgresql.org
Subject: Re: Using a different column name in a foreign table

[Email External/Externe] Caution opening links or attachments/attention lors de 
l'ouverture de liens ou de pièces jointes.

Alanoly Andrews mailto:alano...@invera.com>> writes:
> I see that the syntax for the creation of a foreign table allows you to use a 
> column name in the FT that is different from the one in the base table. Such 
> a "create foreign table" statement executes successfully and creates the FT. 
> But when I query the FT, I get an error wrt to the column that had been 
> renamed. See example below:

> create foreign table tab1_ft (
> id int,
> name char(10) options(column_name 'newname')) server xx
> options(schema_name 'public', table_name 'tab1');

> select * from tab1_ft;

> ERROR:  column "newname" does not exist
> HINT:  Perhaps you meant to reference the column "tab1.name".
> CONTEXT:  Remote SQL command: SELECT id, newname FROM public.tab1

> So, it seems that the when the remote SQL command is composed, the mapping of 
> 'newname' to the 'name' in the base table does not take effect.

Huh?  The CONTEXT line shows what was issued to the remote server, and it's 
very obvious that we *are* asking for "newname", as indeed is also implied by 
the error issued by the remote.  I think you just didn't match the name 
correctly to the actual name on the remote.

regards, tom lane




This e-mail may be privileged and/or confidential, and the sender does not 
waive any related rights and obligations. Any distribution, use or copying of 
this e-mail or the information it contains by other than an intended recipient 
is unauthorized. If you received this e-mail in error, please advise me (by 
return e-mail or otherwise) immediately.

Ce courriel est confidentiel et protégé. L'expéditeur ne renonce pas aux droits 
et obligations qui s'y rapportent. Toute diffusion, utilisation ou copie de ce 
message ou des renseignements qu'il contient par une personne autre que le 
(les) destinataire(s) désigné(s) est interdite. Si vous recevez ce courriel par 
erreur, veuillez m'en aviser immédiatement, par retour de courriel ou par un 
autre moyen.'. If the disclaimer can't be applied, attach the message to a new 
disclaimer message.

Re: Can commands be typed in to view geometry in PgAdmin?

[Ron]

pgAdmin, as the name implies, is really for *administering* Postgresql in a 
GUI manner, *not* for visualizing GIS coordinates.


On 1/21/22 10:26 AM, Shaozhong SHI wrote:
It would be nice to be able to write lines of scripts to tell PgAdmin to 
show some visualisation of geographical features.


Regards,

David

On Fri, 21 Jan 2022 at 15:14, David G. Johnston 
mailto:david.g.johns...@gmail.com>> wrote:


It is amazing how much one can learn by reading documentation. You are
looking for a section that describes how to view, and possibly edit,
data in pgAdmin4.

David J.


On Fri, Jan 21, 2022 at 5:52 AM Shaozhong SHI mailto:shishaozh...@gmail.com>> wrote:

I just wonder whether commands can be typed in PgAdmin to view
geometries.

Regards,

David



--
Angular momentum makes the world go 'round.

Re: Using a different column name in a foreign table

[Adrian Klaver]

On 1/21/22 10:04 AM, Alanoly Andrews wrote:
Following up on my previous message, there was a related problem with FT 
definitions for which I have a workaround. It seems that the new 
column_name after "options" in an FT definition cannot be in upper case. 
See below:
When I define a foreign table like below, I get an error in the 
subsequent select statement:

create foreign table scvmgl_rec (
MGL_CLNT_HOST_NM char(15) options (column_name 'MSG_CLNT_HOST_NM'))
server pglpolaris12018
options (schema_name 'informix', table_name 'sctmsg_rec');
ai01pr00=# select * from scvmgl_rec;
ERROR:  column "MSG_CLNT_HOST_NM" does not exist
CONTEXT:  Remote SQL command: SELECT "MSG_CLNT_HOST_NM" FROM 
informix.sctmsg_rec
But when I define the FT as below, there is no error in the subsequent 
"select".

create foreign table scvmgl_rec (
mgl_clnt_host_nm char(15) options (column_name 'msg_clnt_host_nm'))
server pglpolaris12018
options (schema_name 'informix', table_name 'sctmsg_rec');
So, apparently postgres transmits the remote query with double quotes 
around the upper-case column name; and such a query comes back with 
"column does not exist". But a double quote around a lower-case column 
name does not produce the error.
Is this a bug that needs to be fixed? I always thought that table names 
and column names were case-insensitive with regard to SQL's.


You thought wrong:

https://www.postgresql.org/docs/current/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS

'Quoting an identifier also makes it case-sensitive, whereas unquoted 
names are always folded to lower case. For example, the identifiers FOO, 
foo, and "foo" are considered the same by PostgreSQL, but "Foo" and 
"FOO" are different from these three and each other. (The folding of 
unquoted names to lower case in PostgreSQL is incompatible with the SQL 
standard, which says that unquoted names should be folded to upper case. 
Thus, foo should be equivalent to "FOO" not "foo" according to the 
standard. If you want to write portable applications you are advised to 
always quote a particular name or never quote it.)'



A.A.


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

RE: Using a different column name in a foreign table

[Alanoly Andrews]

Thanks Adrian. I was aware of that.

But...in the case of FT definition, the quotes are mandatory after column_name 
in options. And it is a single quote.
The problem here may be is that the "create foreign table" statement accepts 
only lower case  after options. Remember that the double quotes for the column 
name in the "select" statement is generated by postgres, not by the end user. 
If the double quotes were not present, the query with the upper case would have 
worked.

A.A.

-Original Message-
From: Adrian Klaver [mailto:adrian.kla...@aklaver.com]
Sent: Friday, January 21, 2022 1:14 PM
To: Alanoly Andrews ; Tom Lane ; 
Guillaume Lelarge 
Cc: pgsql-general@lists.postgresql.org
Subject: Re: Using a different column name in a foreign table

[Email External/Externe] Caution opening links or attachments/attention lors de 
l'ouverture de liens ou de pièces jointes.

On 1/21/22 10:04 AM, Alanoly Andrews wrote:
> Following up on my previous message, there was a related problem with
> FT definitions for which I have a workaround. It seems that the new
> column_name after "options" in an FT definition cannot be in upper case.
> See below:
> When I define a foreign table like below, I get an error in the
> subsequent select statement:
> create foreign table scvmgl_rec (
> MGL_CLNT_HOST_NM char(15) options (column_name 'MSG_CLNT_HOST_NM'))
> server pglpolaris12018 options (schema_name 'informix', table_name
> 'sctmsg_rec'); ai01pr00=# select * from scvmgl_rec;
> ERROR:  column "MSG_CLNT_HOST_NM" does not exist
> CONTEXT:  Remote SQL command: SELECT "MSG_CLNT_HOST_NM" FROM
> informix.sctmsg_rec But when I define the FT as below, there is no
> error in the subsequent "select".
> create foreign table scvmgl_rec (
> mgl_clnt_host_nm char(15) options (column_name 'msg_clnt_host_nm'))
> server pglpolaris12018 options (schema_name 'informix', table_name
> 'sctmsg_rec'); So, apparently postgres transmits the remote query with
> double quotes around the upper-case column name; and such a query
> comes back with "column does not exist". But a double quote around a
> lower-case column name does not produce the error.
> Is this a bug that needs to be fixed? I always thought that table
> names and column names were case-insensitive with regard to SQL's.

You thought wrong:

https://www.postgresql.org/docs/current/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS

'Quoting an identifier also makes it case-sensitive, whereas unquoted names are 
always folded to lower case. For example, the identifiers FOO, foo, and "foo" 
are considered the same by PostgreSQL, but "Foo" and "FOO" are different from 
these three and each other. (The folding of unquoted names to lower case in 
PostgreSQL is incompatible with the SQL standard, which says that unquoted 
names should be folded to upper case.
Thus, foo should be equivalent to "FOO" not "foo" according to the standard. If 
you want to write portable applications you are advised to always quote a 
particular name or never quote it.)'

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

This e-mail may be privileged and/or confidential, and the sender does not 
waive any related rights and obligations. Any distribution, use or copying of 
this e-mail or the information it contains by other than an intended recipient 
is unauthorized. If you received this e-mail in error, please advise me (by 
return e-mail or otherwise) immediately.


Ce courriel est confidentiel et protégé. L'expéditeur ne renonce pas aux droits 
et obligations qui s'y rapportent. Toute diffusion, utilisation ou copie de ce 
message ou des renseignements qu'il contient par une personne autre que le 
(les) destinataire(s) désigné(s) est interdite. Si vous recevez ce courriel par 
erreur, veuillez m'en aviser immédiatement, par retour de courriel ou par un 
autre moyen.'. If the disclaimer can't be applied, attach the message to a new 
disclaimer message.

psql and Postgres 7.2

[Adrian Klaver]

In answering a question on SO I discovered this:

\l PRODUCTION
List of databases
Name|  Owner   | Encoding |   Collate   |Ctype| Access 
privileges

+--+--+-+-+---
 production | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |

\c PRODUCTION
FATAL:  database "PRODUCTION" does not exist
Previous connection kept

which I traced back to(from Postgres version 14):

src/bin/psql/command.c

/*
 * Read and interpret an argument to the \connect slash command.
...

/*
  * Ideally we should treat the arguments as SQL identifiers.  But for
  * backwards compatibility with 7.2 and older pg_dump files, we have to
  * take unquoted arguments verbatim (don't downcase them). For now,
  * double-quoted arguments may be stripped of double quotes (as if SQL
  * identifiers).  By 7.4 or so, pg_dump files can be expected to
  * double-quote all mixed-case \connect arguments, and then we can get rid
  * of OT_SQLIDHACK.
*/

Since Postgres 14 pg_dump only goes back to 8.0 and the \d commands in 
psql go back to 7.4, is this something that needs to hang on?


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

Re: Using a different column name in a foreign table

[Tom Lane]

Alanoly Andrews  writes:
> But...in the case of FT definition, the quotes are mandatory after 
> column_name in options. And it is a single quote.

Right.  That string literal is not case-folded, so it has to accurately
match the actual spelling of the remote column name.  Anything else we
might do there would either make things even more confusing, or prevent
you from accessing mixed-case remote names at all.

(Well, maybe we could ignore the SQL standard's mandate that column
option values be string literals, but that's just setting ourselves
up for future problems.)

regards, tom lane

Re: Using a different column name in a foreign table

[Adrian Klaver]

On 1/21/22 10:32 AM, Alanoly Andrews wrote:

Thanks Adrian. I was aware of that.

But...in the case of FT definition, the quotes are mandatory after column_name 
in options. And it is a single quote.
The problem here may be is that the "create foreign table" statement accepts only lower 
case  after options. Remember that the double quotes for the column name in the "select" 
statement is generated by postgres, not by the end user. If the double quotes were not present, the 
query with the upper case would have worked.


I have not looked at the source, but I'm betting it is using something like:

https://www.postgresql.org/docs/14/functions-string.html

quote_ident ( text ) → text

Returns the given string suitably quoted to be used as an identifier in 
an SQL statement string. Quotes are added only if necessary (i.e., if 
the string contains non-identifier characters or would be case-folded). 
Embedded quotes are properly doubled. See also Example 43.1.


quote_ident('Foo bar') → "Foo bar"


So:

select quote_ident('MSG_CLNT_HOST_NM');
quote_ident

 "MSG_CLNT_HOST_NM"


select quote_ident('msg_clnt_host_nm');
   quote_ident
--
 msg_clnt_host_nm




A.A.




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

Re: Using a different column name in a foreign table

[David G. Johnston]

On Fri, Jan 21, 2022 at 11:32 AM Alanoly Andrews 
wrote:

> Thanks Adrian. I was aware of that.
>
> But...in the case of FT definition, the quotes are mandatory after
> column_name in options. And it is a single quote.
>

Using an unqualified "quote" is this kind of discussion should be avoided.

The option itself is just text.  The system basically has two choices.  Use
the text as-is for the identifier, in which case the user would have to
write '"NAME OF COLUMN"' (that's single-quote, double-quote, characters,
double-quote, single-quote) or, as Adrian just said, take the input string
and perform quoting on-the-fly into to turn it into a valid SQL identifier
(which is what quote_ident(text) does).

The later ends up being more user-friendly and prevents, however
unlikely in this situation, SQL-injection problems.

Since the second option preserves the supplied text as an explicit name it
indeed must be exactly what is required and one cannot rely on case-folding
behavior.

This seems like something that should be, but is not, documented for that
option.

David J.

Re: psql and Postgres 7.2

[Tom Lane]

Adrian Klaver  writes:
> src/bin/psql/command.c
> /*
>* Ideally we should treat the arguments as SQL identifiers.  But for
>* backwards compatibility with 7.2 and older pg_dump files, we have to
>* take unquoted arguments verbatim (don't downcase them). For now,
>* double-quoted arguments may be stripped of double quotes (as if SQL
>* identifiers).  By 7.4 or so, pg_dump files can be expected to
>* double-quote all mixed-case \connect arguments, and then we can get rid
>* of OT_SQLIDHACK.
> */

> Since Postgres 14 pg_dump only goes back to 8.0 and the \d commands in 
> psql go back to 7.4, is this something that needs to hang on?

Yeah, I was wondering about that the other day.  I agree that we
probably don't need to worry anymore about reading 7.2 pg_dump
output.  However, people may be used to/depending on the current
behavior in other ways.  It's hard to be sure, but it is worth
noting that it's been a long time since \connect arguments were
only identifiers.  We'd have to consider the impact on the
connstring case, too.

regards, tom lane

Re: psql and Postgres 7.2

[Adrian Klaver]

On 1/21/22 10:49 AM, Tom Lane wrote:

Adrian Klaver  writes:

src/bin/psql/command.c
/*
* Ideally we should treat the arguments as SQL identifiers.  But for
* backwards compatibility with 7.2 and older pg_dump files, we have to
* take unquoted arguments verbatim (don't downcase them). For now,
* double-quoted arguments may be stripped of double quotes (as if SQL
* identifiers).  By 7.4 or so, pg_dump files can be expected to
* double-quote all mixed-case \connect arguments, and then we can get rid
* of OT_SQLIDHACK.
*/



Since Postgres 14 pg_dump only goes back to 8.0 and the \d commands in
psql go back to 7.4, is this something that needs to hang on?


Yeah, I was wondering about that the other day.  I agree that we
probably don't need to worry anymore about reading 7.2 pg_dump
output.  However, people may be used to/depending on the current
behavior in other ways.  It's hard to be sure, but it is worth
noting that it's been a long time since \connect arguments were
only identifiers.  We'd have to consider the impact on the
connstring case, too.


Maybe at this point it comes down to a revision to the documentation 
that indicates the downcasing rules don't apply to identifiers in \connect.




regards, tom lane




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

Re: recording of INDEX creation in tables

[Michael Paquier]

On Fri, Jan 21, 2022 at 01:38:59PM +0100, Matthias Apitz wrote:
> Does the PostgreSQL (11.4 or 13.1) record somewhere in system tables
> the creation of INDEXes (or other objects)?

Hard to say what you are looking for with such a general question.

Would pg_index or pg_indexes be enough?  There are a bunch of system
catalogs and views, like what's in information_schema, that provide
this information.  If you are looking for a record of the timestamp
when objects have been created, there is no such tracking in Postgres
itself, but you could use an event trigger for this purpose.

Here are some links;
https://www.postgresql.org/docs/devel/catalog-pg-index.html
https://www.postgresql.org/docs/devel/view-pg-indexes.html
https://www.postgresql.org/docs/devel/event-triggers.html
--
Michael


signature.asc
Description: PGP signature

Re: How are md5.h: pg_md5_hash() function and below functions working?

[Michael Paquier]

On Sat, Jan 08, 2022 at 08:12:50AM -0800, Adrian Klaver wrote:
> On 1/8/22 05:21, Ali Koca wrote:
>> I can't understand functions in md5.h, these are seemingly little bit
>> weird. Such as:
>>  /* Utilities common to all the MD5 implementations,
>>  as of md5_common.c */
>>  extern bool pg_md5_hash(const void *buff, size_t len, char *hexsum);
>>  extern bool pg_md5_binary(const void *buff, size_t len, void *outbuf);
>>  extern bool pg_md5_encrypt(const char *passwd, const char *salt,
>>     size_t salt_len, char *buf);
> 
> What they do is explained in md5_common.c.

pg_md5_hash() and pg_md5_binary() do the same thing, by computing a
MD5.  pg_md5_hash() computes it as a string in hex format, meaning
that it is 33-character long with the final trailing '\0'.
pg_md5_binary() computes 16 raw bytes.

pg_md5_encrypt() is a utility wrapper that does the operation of
pg_md5_hash() based on a password and a salt, used at authentication
time for MD5.  Its result is a 36-byte long string, prefixed with
"md5" and a 33-byte long hex string.  Those routine names are
historic.
--
Michael


signature.asc
Description: PGP signature

Re: recording of INDEX creation in tables

[David G. Johnston]

On Fri, Jan 21, 2022 at 5:39 AM Matthias Apitz  wrote:

>
> Hello,
>
> Does the PostgreSQL (11.4 or 13.1) record somewhere in system tables
> the creation of INDEXes (or other objects)?
>
>
13.1? Really?

Features are not point-release dependent so v11 or v13 suffices when trying
to figure out whether some feature exists.

PostgreSQL does not provide auditing-type information in its system tables,
they simply represent the current state.  Some features can be used to
manually (possibly through community-provided extensions) record such data
to non-system tables (which is usually fine with the proper permission
setup) or the server log.

David J.