[SQL] Re: [HACKERS] Facing authentication error on postgres 9.2 - dblink functions

[Andrew Dunstan]

On 02/06/2013 08:09 AM, Dev Kumkar wrote:

Hello Everyone,

I am using postgres 9.2 and when executing function dblink facing a 
fatal error while trying to execute dblink_connect as follows:


/SELECT * FROM dblink_connect('host=127.0.0.1 port=5432 
dbname=postgres password=test')/


*ERROR*: could not establish connection DETAIL: FATAL: password 
authentication failed for user NETWORK SERVICE


What this error is related to? Do I need to modify pg_hba.conf file by 
any chance?


Thanks..




Do NOT send questions to multiple lists. That is a waste of everybody's 
time. So do NOT follow up this email. This question belongs on 
pgsql-general. If you have further questions pleease ask there.


The short answer is that you need to provide the user name in your 
connect string.


cheers

andrew


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

Re: [HACKERS] [SQL] Case Preservation disregarding case

[Andrew Dunstan]

There was some discussion a couple of years ago on the -hackers list 
about it, so you might like to review the archives. The consensus seemed 
to be that behaviour would need to be set no later than createdb time.  
The options I thought of were:


. current postgres behaviour (we need to do this for legacy reasons, of 
course, as well as to keep happy the legions who hate using upper case 
for anything)
. strictly spec compliant (same as current behaviour, but folding to 
upper case for unquoted identifiers rather than lower)
. fully case sensitive even for unquoted identifiers (not spec compliant 
at all, but nevertheless possibly attractive especially for people 
migrating from MS SQLServer, where it is an option, IIRC).


To this you propose, as I understand it, to have a fourth possibility 
which would be spec compliant for comparison purposes but would label 
result set columns with the case preserved name originally used (or 
would you use the casing used in the query?).


These could be accomplished I think with a second catalog column like 
you suggest, in a number of places, but making sure all the code paths 
were covered might be somewhat laborious. We could probably add the 
second option without being nearly so invasive, though, and some people 
might feel that that would be sufficient.


cheers

andrew

Chuck McDevitt wrote:

We treated quoted identifiers as case-specific, as the spec requires.

In the catalog, we stored TWO columns... The column name with case
converted as appropriate (as PostgreSQL already does), used for looking
up the attribute,
And a second column, which was the column name with the case exactly as
entered by the user.

So, your example would work just fine.


-Original Message-
From: Tom Lane [mailto:[EMAIL PROTECTED] 
Sent: Monday, October 30, 2006 10:35 PM

To: Chuck McDevitt
Cc: beau hargis; pgsql-sql@postgresql.org; pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] [SQL] Case Preservation disregarding case
sensitivity?

Chuck McDevitt [EMAIL PROTECTED] writes:
  

At Teradata, we certainly interpreted the spec to allow


case-preserving,
  

but case-insensitive, identifiers.



Really?

As I see it, the controlling parts of the SQL spec are (SQL99 sec 5.2)

26) A regular identifier and a delimited identifier are
equivalent if the identifier body of the regular
identifier
(with every letter that is a lower-case letter replaced by
the
corresponding upper-case letter or letters) and the
delimited
identifier body of the delimited identifier (with all
occurrences of quote replaced by quote symbol and all
occurrences of doublequote symbol replaced by double
quote),
considered as the repetition of a character string literal
that specifies a character set specification of
SQL_IDENTIFIER
and an implementation-defined collation that is sensitive to
case, compare equally according to the comparison rules in
Subclause 8.2, comparison predicate.

27) Two delimited identifiers are equivalent if their
delimited
identifier bodys, considered as the repetition of a
character
string literal that specifies a character set
specification
of SQL_IDENTIFIER and an implementation-defined collation
that is sensitive to case, compare equally according to the
comparison rules in Subclause 8.2, comparison predicate.

Note well the sensitive to case bits there.  Now consider

CREATE TABLE tab (
foobar int,
FooBar timestamp,
FOOBAR varchar(3)
);

We can *not* reject this as containing duplicate column names, else we
have certainly violated rule 27.  Now what will you do with

SELECT fooBar FROM tab;

?  The spec is unquestionably on the side of you selected the varchar
column; historical Postgres practice is on the side of you selected
the int column.  AFAICS a case-insensitive approach would have to
fail with some I can't identify which column you mean error.  I am
interested to see where you find support for that in the spec...





---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [SQL] [HACKERS] plpgsql doesn't coerce boolean expressions to boolean

[Andrew Dunstan]

Tom Lane wrote:

Following up this gripe
http://archives.postgresql.org/pgsql-sql/2003-09/msg00044.php
I've realized that plpgsql just assumes that the test expression
of an IF, WHILE, or EXIT statement is a boolean expression.  It
doesn't take any measures to ensure this is the case or convert
the value if it's not the case.  This seems pretty bogus to me.
However ... with the code as it stands, for pass-by-reference datatypes
any nonnull value will appear TRUE, while for pass-by-value datatypes
any nonzero value will appear TRUE.  I fear that people may actually be
depending on these behaviors, particularly the latter one which is
pretty reasonable if you're accustomed to C.  So while I'd like to throw
an error if the argument isn't boolean, I'm afraid of breaking people's
function definitions.
Here are some possible responses, roughly in order of difficulty
to implement:
1. Leave well enough alone (and perhaps document the behavior).

2. Throw an error if the expression doesn't return boolean.

3. Try to convert nonbooleans to boolean using plpgsql's usual method
  for cross-type coercion, ie run the type's output proc to get a
  string and feed it to bool's input proc.  (This seems unlikely to
  avoid throwing an error in very many cases, but it'd be the most
  consistent with other parts of plpgsql.)
4. Use the parser's coerce_to_boolean procedure, so that nonbooleans
  will be accepted in exactly the same cases where they'd be accepted
  in a boolean-requiring SQL construct (such as CASE).  (By default,
  none are, so this isn't really different from #2.  But people could
  create casts to boolean to override this behavior in a controlled
  fashion.)
Any opinions about what to do?

 

It won't bite me so maybe I don't have a right to express an opinion :-)

plpgsql is not C - it appears to be in the Algol/Pascal/Ada family, 
which do tend to avoid implicit type conversion.

On that basis, option 2 seems like it might be the right answer and also 
the one most likely to break lots of existing functions. Maybe the right 
thing would be to deprecate relying on implicit conversion to boolean 
for one release cycle and then make it an error.

cheers

andrew



---(end of broadcast)---
TIP 6: Have you searched our list archives?
  http://archives.postgresql.org