Re: deallocate statement failing

[rob stone]

On Fri, 2020-02-07 at 00:48 -0500, Tom Lane wrote:
> rob stone  writes:
> > 2020-02-07 15:42:55 AEDT ERROR:  syntax error at or near
> > "'AIT1581050529'" at character 13
> > 2020-02-07 15:42:55 AEDT STATEMENT:  DEALLOCATE  'AIT1581050529'
> > The deallocate statement fails. It doesn't matter if it is just
> > "DEALLOCATE" or "DEALLOCATE PREPARE".
> 
> I think you want double quotes not single quotes.  The argument
> of DEALLOCATE is an SQL identifier, not a string literal.
> 
>   regards, tom lane


That worked. Thanks.

Re: deallocate statement failing

[Tom Lane]

rob stone  writes:
> 2020-02-07 15:42:55 AEDT ERROR:  syntax error at or near
> "'AIT1581050529'" at character 13
> 2020-02-07 15:42:55 AEDT STATEMENT:  DEALLOCATE  'AIT1581050529'

> The deallocate statement fails. It doesn't matter if it is just
> "DEALLOCATE" or "DEALLOCATE PREPARE".

I think you want double quotes not single quotes.  The argument
of DEALLOCATE is an SQL identifier, not a string literal.

regards, tom lane

Re: deallocate statement failing

[Adrian Klaver]

On 2/6/20 9:02 PM, rob stone wrote:

Hello,

Version:-


PostgreSQL 12.1 (Debian 12.1-2) on x86_64-pc-linux-gnu, compiled by gcc
(Debian 9.2.1-22) 9.2.1 20200104, 64-bit

Log:-

2020-02-07 15:42:55 AEDT LOG:  statement: SELECT COUNT(*) AS ps_count
FROM pg_prepared_statements WHERE name = 'AIT1581050529'
2020-02-07 15:42:55 AEDT ERROR:  syntax error at or near
"'AIT1581050529'" at character 13
2020-02-07 15:42:55 AEDT STATEMENT:  DEALLOCATE  'AIT1581050529'



Try:

DEALLOCATE  AIT1581050529;



The deallocate statement fails. It doesn't matter if it is just
"DEALLOCATE" or "DEALLOCATE PREPARE".
We put in the count statement to make sure the prepared statement still
existed and only issued the "DEALLOCATE" if ps_count is greater then
zero.

Does anybody know why it is giving this syntax error?

TIA,
Rob







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

deallocate statement failing

[rob stone]

Hello,

Version:-


PostgreSQL 12.1 (Debian 12.1-2) on x86_64-pc-linux-gnu, compiled by gcc
(Debian 9.2.1-22) 9.2.1 20200104, 64-bit

Log:-

2020-02-07 15:42:55 AEDT LOG:  statement: SELECT COUNT(*) AS ps_count
FROM pg_prepared_statements WHERE name = 'AIT1581050529'
2020-02-07 15:42:55 AEDT ERROR:  syntax error at or near
"'AIT1581050529'" at character 13
2020-02-07 15:42:55 AEDT STATEMENT:  DEALLOCATE  'AIT1581050529'


The deallocate statement fails. It doesn't matter if it is just
"DEALLOCATE" or "DEALLOCATE PREPARE".
We put in the count statement to make sure the prepared statement still
existed and only issued the "DEALLOCATE" if ps_count is greater then
zero.

Does anybody know why it is giving this syntax error?

TIA,
Rob

Re: POLL: Adding transaction status to default psql prompt

[Ken Tanzer]

>
> Em qua, 5 de fev de 2020 às 23:55, Vik Fearing 
> escreveu:
>
>>
>> Please answer +1 if you want or don't mind seeing transaction status by
>> default in psql or -1 if you would prefer to keep the current default.
>
>
+1

I liked the idea just reading about it, but thought it would be good form
to at least try it out before voting.  If I read the patch right, people
can try this out by setting their prompt without having to change their
.psqlrc file:

\set PROMPT1 ''%/%R%x%# '

Having done so, I'm still a +1!

Cheers,
Ken


-- 
AGENCY Software
A Free Software data system
By and for non-profits
*http://agency-software.org/ *
*https://demo.agency-software.org/client
*
ken.tan...@agency-software.org
(253) 245-3801

Subscribe to the mailing list
 to
learn more about AGENCY or
follow the discussion.

Re: POLL: Adding transaction status to default psql prompt

[Nikolay Samokhvalov]

On Wed, Feb 5, 2020 at 8:05 PM Vik Fearing  wrote:

> Because it isn't always easy to modify the .psqlrc file.  This is
> especially true if you frequently connect to other people's systems.
>

Exactly. You can tune your psql a lot, including excellent pspg,
but then you frequently troubleshoot other people's systems, you deal with
defaults.

And finally, the default settings is what most people will always use.

Re: Unable to startup postgres: Could not read from file "pg_clog/00EC"

[Thomas Munro]

On Fri, Feb 7, 2020 at 1:47 AM Nick Renders  wrote:
> Thank you for the feedback, Alvaro.
>
> Unfortunately, the database is no longer "dumpable". We were able to do
> a pg_dump yesterday morning (12 hours after the crash + purging the
> pg_clog) but if we try one now, we get the following error:
>
> unexpected chunk number 1 (expected 0) for toast value 8282331 in
> pg_toast_38651
>
> Looking at our data, there seem to be 6 tables that have corrupt
> records. Doing a SELECT * for one of those records, will return a
> similar error:
>
> missing chunk number 0 for toast value 8288522 in pg_toast_5572299
>
>
> What is the best way to go from here? Is tracking down these corrupt
> records and deleting them the best / only solution?
> Is there a way to determine of there are issues with new data (after the
> crash)?
>
> Any help and advice is very much appreciated.

This error indicates that the file did exist already, it was just
shorter than we expected:

2020-02-04 15:20:44 CET DETAIL:  Could not read from file
"pg_clog/00EC" at offset 106496: Undefined error: 0.

What was the length of the file before you overwrote it? Are there
00EB and 00ED files, and if so what size?  When your server rebooted,
did crash recovery run or had it shut down cleanly?  Do you know if
the machine lost power, or the kernel crashed, or if it was a normal
reboot?  What are your settings for "fsync" and "wal_sync_method"?
What is the output of pg_controldata -D pgdata?  I wonder if that part
of the clog file was supposed to be created before the checkpoint (ie
the checkpoint is somehow borked), or was supposed to be created
during recovery after that checkpoint (something else is borked, but I
don't know what), or if the xid is somehow corrupted.

Here's a dirty trick that might help rescue some data.  Assuming you
have a copy of the original file before you zeroed it, you could write
a 256kb file full of 0x55 (that's 01010101 and represents 4 commits,
so if you fill the file up with that it means 'all transactions in
this range committed', which is probably closer to the truth than all
zeroes), and then copy the original shorter file over the top of it,
so that at least the range of transactions represented by the earlier
part of the file that did make it to disk are preserved, and we have
just have bogus force-everything-to-look-committed data after that.
But as Alvaro said, this is a pretty bad situation, this is key meta
data used to interpret all other data files, so all bets are off here,
this is restore-from-backups territory.

Re: POLL: Adding transaction status to default psql prompt

[Fabrízio de Royes Mello]

Em qua, 5 de fev de 2020 às 23:55, Vik Fearing 
escreveu:

>
> Please answer +1 if you want or don't mind seeing transaction status by
> default in psql or -1 if you would prefer to keep the current default.
>

+1

-- 
   Fabrízio de Royes Mello Timbira - http://www.timbira.com.br/
   PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento

Re: Function not imported in Entity Framework

[Adrian Klaver]

On 2/6/20 1:36 AM, Vikram Sah wrote:

Dear sir/mam,

I want to integrate PostgreSQL database to entity framework in asp.net 
 using database first approach. but when try to add 
function then error occurred:


*Error 6046: Unable to generate function import return type of the store 
function.*


This really a question for the EntityFramework maintainers/users. Still 
I did find this:


https://stackoverflow.com/questions/24161005/error-6046-unable-to-generate-function-import-return-type-of-the-store-function





I have following configuration:

PostgreSQL 11.6,
npgsql v.4.0.9.0,
EntityFramework6.Npgsql 3.2.1.1,
in .net framework 4.5.2

*My function in postgresql database is :*

CREATE OR REPLACE FUNCTION dbo.EmployeeDetail ()
RETURNS TABLE (
emp_name VARCHAR,
emp_Id UUID
)
AS $$
BEGIN
RETURN QUERY SELECT
"EmployeName",
"EmployeeID"
FROM
dbo.Employee;
END; $$

LANGUAGE 'plpgsql';


Can you pls help! I did more researches but no luck.


Thanks!






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

Re: POLL: Adding transaction status to default psql prompt

[Charles Clavadetscher]

Please answer +1 if you want or don't mind seeing transaction status by
default in psql or -1 if you would prefer to keep the current default.


+1

--
Charles Clavadetscher
Swiss PostgreSQL Users Group
Treasurer
Spitzackerstrasse 9
CH - 8057 Zürich

http://www.swisspug.org

+---+
|   __  ___ |
|/)/  \/   \|
|   ( / ___\)   |
|\(/ o)  ( o)   )   |
| \_  (_  )   \ ) _/|
|   \  /\_/\)/  |
|\/   |
| _|  | |
| \|_/  |
|   |
| Swiss PGDay 2020  |
|  18/19.06.2020|
|  HSR Rapperswil   |
|   |
+---+

Re: POLL: Adding transaction status to default psql prompt

[Victor Yegorov]

чт, 6 февр. 2020 г. в 04:55, Vik Fearing :

> Please answer +1 if you want or don't mind seeing transaction status by
> default in psql or -1 if you would prefer to keep the current default.
>


+1

-- 
Victor Yegorov

Re: Unable to startup postgres: Could not read from file "pg_clog/00EC"

[Nick Renders]

Thank you for the feedback, Alvaro.

Unfortunately, the database is no longer "dumpable". We were able to do 
a pg_dump yesterday morning (12 hours after the crash + purging the 
pg_clog) but if we try one now, we get the following error:


	unexpected chunk number 1 (expected 0) for toast value 8282331 in 
pg_toast_38651


Looking at our data, there seem to be 6 tables that have corrupt 
records. Doing a SELECT * for one of those records, will return a 
similar error:


missing chunk number 0 for toast value 8288522 in pg_toast_5572299


What is the best way to go from here? Is tracking down these corrupt 
records and deleting them the best / only solution?
Is there a way to determine of there are issues with new data (after the 
crash)?


Any help and advice is very much appreciated.

Thanks,


Nick Renders


On 5 Feb 2020, at 12:51, Alvaro Herrera wrote:


On 2020-Feb-05, Nick Renders wrote:

Is there anything specific I should check in our postgres 
installation /

database to make sure it is running ok now? Anyway to see what the
consequences were of purging that one pg_clog file?


Losing pg_clog files is pretty bad, and should not happen; then again,
this might have been something else (ie. the file was maybe not lost).
That said, wrongly overwriting files is even worse.

By zeroing an existing pg_clog file, you marked a bunch of 
transactions

as aborted.  Your data is now probably inconsistent, if not downright
corrupt.  I would be looking for my most recent backup ...

If you're very lucky, your database might be pg_dumpable.  I would try
that, followed by restoring it in a separate clean instance and seeing
what happens.

--
Álvaro Herrerahttps://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: POLL: Adding transaction status to default psql prompt

[Ray O'Donnell]

On 06/02/2020 02:54, Vik Fearing wrote:
> Hello,
> 
> I proposed a patch to add %x to PROMPT1 and PROMPT2 by default in psql.
> 
> The effect of this is:
> 
> - nothing at all when not in a transaction,
> - adding a '*' when in a transaction or a '!' when in an aborted
>   transaction.
> 
> Before making a change to a long-time default, a poll in this group was
> requested.
> 
> Please answer +1 if you want or don't mind seeing transaction status by
> default in psql or -1 if you would prefer to keep the current default.

+1


-- 
Raymond O'Donnell // Galway // Ireland
r...@rodonnell.ie

Re: POLL: Adding transaction status to default psql prompt

[Daniel Westermann (DWE)]

>On Thu, Feb 06, 2020 at 03:54:48AM +0100, Vik Fearing wrote:
>> Please answer +1 if you want or don't mind seeing transaction status by
>> default in psql or -1 if you would prefer to keep the current default.
>
>+1

+1

Daniel

Re: POLL: Adding transaction status to default psql prompt

[hubert depesz lubaczewski]

On Thu, Feb 06, 2020 at 03:54:48AM +0100, Vik Fearing wrote:
> Please answer +1 if you want or don't mind seeing transaction status by
> default in psql or -1 if you would prefer to keep the current default.

+1

depesz

Function not imported in Entity Framework

[Vikram Sah]

Dear sir/mam,

I want to integrate PostgreSQL database to entity framework in asp.net
using database first approach. but when try to add function then
error occurred:

*Error 6046: Unable to generate function import return type of the store
function.*

I have following configuration:

PostgreSQL 11.6,
npgsql v.4.0.9.0,
EntityFramework6.Npgsql 3.2.1.1,
in .net framework 4.5.2

*My function in postgresql database is :*

CREATE OR REPLACE FUNCTION dbo.EmployeeDetail ()
RETURNS TABLE (
emp_name VARCHAR,
emp_Id UUID
)
AS $$
BEGIN
RETURN QUERY SELECT
"EmployeName",
"EmployeeID"
FROM
dbo.Employee;
END; $$

LANGUAGE 'plpgsql';


Can you pls help! I did more researches but no luck.


Thanks!