Strange behavior with missing column in SQL function

[Marcelo Lacerda]

Here's the code that reproduces the behavior:
http://paste.debian.net/1035412/

I have already discussed this in the IRC channel but there doesn't seem to
be a consensus on whether this is a bug here's a brief transcript of
RhodiumToad's opinion:

> this isn't new, goes back to 9.1 at least
> basically, the error path in sql_fn_post_column_ref is a bit confused.
> seeing r.c it tries to resolve it as parameter.field, fails, and rather
than reporting the error directly as being a missing field, it just returns
with the reference unresolved
>then the outer parser code, having failed to resolve it as table.column
and having had the hook function not override it, reports it on the
assumption that it's a missing table
> so it's probably been this way for as long as named parameters have
worked in language sql
> msl09: as far as I can tell it's just giving the wrong error in an error
path, everything that's supposed to work does work
> msl09: but the error is definitely misleading

My question is "Is this a bug? Should it be reported?"

Re: Strange behavior with missing column in SQL function

[Marcelo Lacerda]

> CREATE OR REPLACE FUNCTION myfunction(myrow mytable)
> RETURNS INTEGER AS $$
> SELECT myrow.c + myrow.b FROM myrow;
> $$ LANGUAGE sql;

> where "myrow" is a table with a different set of column names from
> "mytable".  The existing behavior for that is to seek the column name
> in "myrow" (the table), failing that to seek it in the parameter,
> and only to throw an error if both fail.
Wow I never thought this would be possible. why didn't the designers of the
language use myrow mytable%ROWTYPE for rows of a table as a parameter,
given that it's a valid type in PL/PGSQL? I figure that way it would have
been way easier to disambiguate the definitions.

check_function_bodies not doing much

[Marcelo Lacerda]

I was trying to get postgres to warn me that I'm referencing a table that
it doesn't exists inside a function so I was told on the IRC to check the
setting "check_function_bodies", however when I use it in a plpgsql
function it doesn't actually check if the tables in the body exist. Is this
the correct behavior?

Example:
http://paste.debian.net/1037080/

Re: check_function_bodies not doing much

[Marcelo Lacerda]

That's a whole different nightmare that I'm expecting.

"Yep I double-checked all my functions to see if any would break if I
change this field mytable.a into 2 fields mytable.a1 and mytable.a2 and
everything is ok."

*1 month later*

"Why is this error log for this application that I wrote one year ago so
big? I haven't changed anything!"

Error table mytable has no column a
Error table mytable has no column a
Error table mytable has no column a
...

It's frustrating that the references that a function make to the tables and
fields it access aren't taken in account for the validation of whether a
change to the structure of the database breaks the APIs that the database
exposes.

On Tue, Aug 7, 2018 at 6:44 PM Merlin Moncure  wrote:

> On Tue, Aug 7, 2018 at 2:31 PM Tom Lane  wrote:
> >
> > Marcelo Lacerda  writes:
> > > I was trying to get postgres to warn me that I'm referencing a table
> that
> > > it doesn't exists inside a function so I was told on the IRC to check
> the
> > > setting "check_function_bodies", however when I use it in a plpgsql
> > > function it doesn't actually check if the tables in the body exist. Is
> this
> > > the correct behavior?
> >
> > Yes.  It's supposed to be a syntax check, not a check that the function
> > would work when executed.  (Depending on the particular PL you're using,
> > which you didn't mention, it might be a pretty weak syntax check too.)
> >
> > An example of why a thorough check would be inadvisable is that a trigger
> > function might contain references to OLD and NEW that are in code paths
> > protected by checks on the trigger event type.  That could be perfectly
> > OK, but a static check couldn't tell.
> >
> > I believe there are some external tools floating around that check things
> > more aggressively, and hence with a higher rate of false positives.
>
> The only valid use of this GUC that I can think of is to work around
> this problem;
> postgres=# create or replace function f() returns void as
> $$
>   create temp table x(id int);
>   delete from x;
> $$ language sql;
> ERROR:  relation "x" does not exist
>
> ...I've since given up on writing plain sql functions except for
> inline cases though so I don't use it anymore.  Static resolution of
> tables is not very useful since the state of the database as the time
> of function creation is different than what it might be when the
> function is run (as opposed to compiled languages obviously).
>
> merlin
>

Re: check_function_bodies not doing much

[Marcelo Lacerda]

I'll take a look at it. Thanks for the recommendation.

On Tue, Aug 7, 2018 at 7:22 PM Pavel Stehule 
wrote:

>
>
> 2018-08-08 0:02 GMT+02:00 Marcelo Lacerda :
>
>> That's a whole different nightmare that I'm expecting.
>>
>
>
>
>>
>> "Yep I double-checked all my functions to see if any would break if I
>> change this field mytable.a into 2 fields mytable.a1 and mytable.a2 and
>> everything is ok."
>>
>> *1 month later*
>>
>> "Why is this error log for this application that I wrote one year ago so
>> big? I haven't changed anything!"
>>
>> Error table mytable has no column a
>> Error table mytable has no column a
>> Error table mytable has no column a
>> ...
>>
>> It's frustrating that the references that a function make to the tables
>> and fields it access aren't taken in account for the validation of whether
>> a change to the structure of the database breaks the APIs that the database
>> exposes.
>>
>
> This cannot be done due possible dynamic SQL. And this issue solve
> plpgsql_check really well.
>
> Regards
>
> Pavel
>
>
>>
>> On Tue, Aug 7, 2018 at 6:44 PM Merlin Moncure  wrote:
>>
>>> On Tue, Aug 7, 2018 at 2:31 PM Tom Lane  wrote:
>>> >
>>> > Marcelo Lacerda  writes:
>>> > > I was trying to get postgres to warn me that I'm referencing a table
>>> that
>>> > > it doesn't exists inside a function so I was told on the IRC to
>>> check the
>>> > > setting "check_function_bodies", however when I use it in a plpgsql
>>> > > function it doesn't actually check if the tables in the body exist.
>>> Is this
>>> > > the correct behavior?
>>> >
>>> > Yes.  It's supposed to be a syntax check, not a check that the function
>>> > would work when executed.  (Depending on the particular PL you're
>>> using,
>>> > which you didn't mention, it might be a pretty weak syntax check too.)
>>> >
>>> > An example of why a thorough check would be inadvisable is that a
>>> trigger
>>> > function might contain references to OLD and NEW that are in code paths
>>> > protected by checks on the trigger event type.  That could be perfectly
>>> > OK, but a static check couldn't tell.
>>> >
>>> > I believe there are some external tools floating around that check
>>> things
>>> > more aggressively, and hence with a higher rate of false positives.
>>>
>>> The only valid use of this GUC that I can think of is to work around
>>> this problem;
>>> postgres=# create or replace function f() returns void as
>>> $$
>>>   create temp table x(id int);
>>>   delete from x;
>>> $$ language sql;
>>> ERROR:  relation "x" does not exist
>>>
>>> ...I've since given up on writing plain sql functions except for
>>> inline cases though so I don't use it anymore.  Static resolution of
>>> tables is not very useful since the state of the database as the time
>>> of function creation is different than what it might be when the
>>> function is run (as opposed to compiled languages obviously).
>>>
>>> merlin
>>>
>>
>

Clarification on the release notes of postgresql 12 regarding pg_upgrade

[Marcelo Lacerda]

There are a few instances where the release notes seem to indicate that the
administrator should use pg_dump to upgrade a database so that improvements
on btree can be available.

Here are they:

1.

>In new btree indexes, the maximum index entry length is reduced by eight
bytes, to improve handling of duplicate entries (Peter Geoghegan)
-

> This means that a REINDEX
 operation on an index
pg_upgrade'd from a previous release could potentially fail.
-
- 2.
>Improve performance and space utilization of btree indexes with many
duplicates (Peter Geoghegan, Heikki Linnakangas)
>...
>Indexes pg_upgrade'd from previous releases will not have these benefits.

3.
>Allow multi-column btree indexes to be smaller (Peter Geoghegan, Heikki
Linnakangas)
>...
>Indexes pg_upgrade'd from previous releases will not have these benefits.


My questions are:

1. Is this a current limitation of pg_upgrade that will be dealt afterwards?

2. Are we going to see more of such cases were pg_upgrade leaves the
database incompatible with newer features.

3. What's the recommendation for administrators with databases that are too
large to be upgraded with pg_dump?

Problem enabling LDAP login

[Marcelo Lacerda]

I'm having some trouble configuring ldap login to postgres. I have
configured LDAP on pg_hba.conf and postgres picks up the correct
configuration during login but I get an error message whenever I attempt to
login with psql to a database named teste.

psql: error: could not connect to server: FATAL:  LDAP authentication
failed for user "my_user_id"

Looking at the log I get a more detailed message but it isn't very useful
either

2021-02-22 10:41:29 -03:172.17.1.139(54178):my_user_id@teste:[50727]: LOG:
 could not search LDAP for filter "(sAMAccountName=my_user_id)" on server
"my_ldap_server": Operations error
2021-02-22 10:41:29 -03:172.17.1.139(54178):my_user_id@teste:[50727]:
DETAIL:  LDAP diagnostics: 04DC: LdapErr: DSID-0C09075A, comment: In
order to perform this operation a successful bind must be completed on the
connection., data 0, v1db1
2021-02-22 10:41:29 -03:172.17.1.139(54178):my_user_id@teste:[50727]:
FATAL:  LDAP authentication failed for user "my_user_id"
2021-02-22 10:41:29 -03:172.17.1.139(54178):my_user_id@teste:[50727]:
DETAIL:  Connection matched pg_hba.conf line 96: "hostall
all 172.17.1.0/24 ldap ldapserver="my_ldap_server"
ldapbasedn="mybasedn" ldapsearchattribute="sAMAccountName"
ldapbinddn="CN=my_bind_dn" ldapbindpasswd="my_bind_password"

Searching postgres source code I was able to find where the problem
happens[1] however it makes things a bit more confusing because from my
understanding postgres was able to successfully bind to the LDAP server in
line 2676 but on line 2700 it reported that a bind was necessary before
querying.

Looking at the tcp packages with tcpdump I was able to notice that openldap
does in fact receive my_user_id information from the server but for some
reason it tries to perform another operation afterwards and fails.

Can anyone help me with this?

[1] -
https://github.com/postgres/postgres/blob/ca3b37487be333a1d241dab1bbdd17a211a88f43/src/backend/libpq/auth.c#L2700

Re: Re: Problem enabling LDAP login

[Marcelo Lacerda]

Thank you joão, that solved the problem!

In-depth commercial postgresql training

[Marcelo Lacerda]

Our company is looking for commercial training in postgresql. We want a
training option that's as in-depth as possible (going as far as being able
to read and patch postgresql source code). Is there any company that offers
something like that?

Another important thing to mention is that due to legal reasons we can't
make payments in any currency other than BRL.