Re: [GENERAL] Replication slot and pg_rewind

[Michael Paquier]

On Tue, Jun 6, 2017 at 1:52 PM, Bhattacharyya, Subhro
 wrote:
> Our expectation is that slave will be able to sync with the new master with 
> the help of whatever WALs are present in the new master due to replication 
> slots.
> Can pg_rewind still work without WAL archiving in this scenario.

I see. Yes, the slot on the old primary would keep retaining WAL, and
the promoted standby would stop sending feedback once it has switched
to a new timeline so that should work. Don't forget to drop the drop
on the old primary after pg_rewind has been run, you don't want to
bloat its pg_xlog with useless data.
-- 
Michael


-- 
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] Replication slot and pg_rewind

[Bhattacharyya, Subhro]

Our cluster works as follows:

We do not promote the slave while the primary is up.

During an update scenario, when the master goes down the slave is promoted to 
master only if there is no replication lag.

As a result, we do not have any data difference till now.

Transactions now continue on the newly promoted master thus creating a 
difference in data on the two nodes.

When the original master, post update comes back as slave, instead of taking a 
pg_basebackup, we use pg_rewind.

Our expectation is that slave will be able to sync with the new master with the 
help of whatever WALs are present in the new master due to replication slots.

Can pg_rewind still work without WAL archiving in this scenario.

Thanks, Subhro

-Original Message-
From: Michael Paquier [mailto:michael.paqu...@gmail.com] 
Sent: Tuesday, June 6, 2017 8:50 AM
To: Bhattacharyya, Subhro 
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Replication slot and pg_rewind

On Tue, Jun 6, 2017 at 12:03 PM, Bhattacharyya, Subhro
 wrote:
> We are using the replication slot and pg_rewind feature of postgresql 9.6
> Our cluster consists of 1 master and 1 slave node.
>
> The replication slot feature allows the master to keep as much WAL as is
> required by the slave.
>
> The pg_rewind command uses WALs to bring the slave in sync with the master.
> By using replication slots there are always enough WAL in the pg_xlog.
>
> In this case is it safe to use pg_rewind without WAL archiving?
> Can there be a situation where pg_rewind fails?

When pg_rewind runs it looks at the WAL from the last checkpoint
before WAL diverged on the *target* node, not the source. So retaining
the WAL data on the primary after the standby has been promoted makes
little sense from this point of view. Even worse, once the promoted
standby decides to recycle the past WAL segments you won't be able to
do a rewind of the previous primary because there is no way to know
what are the blocks modified on the standby since the point of
divergence.
-- 
Michael

-- 
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] Replication slot and pg_rewind

[Michael Paquier]

On Tue, Jun 6, 2017 at 12:03 PM, Bhattacharyya, Subhro
 wrote:
> We are using the replication slot and pg_rewind feature of postgresql 9.6
> Our cluster consists of 1 master and 1 slave node.
>
> The replication slot feature allows the master to keep as much WAL as is
> required by the slave.
>
> The pg_rewind command uses WALs to bring the slave in sync with the master.
> By using replication slots there are always enough WAL in the pg_xlog.
>
> In this case is it safe to use pg_rewind without WAL archiving?
> Can there be a situation where pg_rewind fails?

When pg_rewind runs it looks at the WAL from the last checkpoint
before WAL diverged on the *target* node, not the source. So retaining
the WAL data on the primary after the standby has been promoted makes
little sense from this point of view. Even worse, once the promoted
standby decides to recycle the past WAL segments you won't be able to
do a rewind of the previous primary because there is no way to know
what are the blocks modified on the standby since the point of
divergence.
-- 
Michael


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

[GENERAL] Replication slot and pg_rewind

[Bhattacharyya, Subhro]

We are using the replication slot and pg_rewind feature of postgresql 9.6

Our cluster consists of 1 master and 1 slave node.

The replication slot feature allows the master to keep as much WAL as is 
required by the slave.

The pg_rewind command uses WALs to bring the slave in sync with the master.
By using replication slots there are always enough WAL in the pg_xlog.

In this case is it safe to use pg_rewind without WAL archiving?
Can there be a situation where pg_rewind fails?

Regards, Subhro

Re: [GENERAL] Help with restoring a dump in Tar format? (dependencies/ordering)

[Adrian Klaver]

On 06/05/2017 05:59 PM, Ken Tanzer wrote:




Not sure why just know that if I stay within the guidelines it
works, if I do not its does not work:)


That's fair enough, leaving aside the curiosity part.  Usually though 
the things you can't do just aren't allowed.  It's easier to overlook 
something that you shouldn't (but can) do!


Yes, what you ran into is just a subset of a bigger issue. That being, 
there are many ways you can dump a database and not get what you wanted 
on the restore. Another example, that is similar, is using the -n switch 
to pg_dump when you have cross schema references in the schema you did dump.





Ken








--
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] Help with restoring a dump in Tar format? (dependencies/ordering)

[Ken Tanzer]

>
> > So I can switch to Custom format for future backups.  But regarding the
> > existing backups I have in Tar format, is there any way to successfully
> > restore them?
>
> FWIW, the business with making and editing a list file should work just
> fine with a tar-format dump, not only with a custom-format dump.  The
> metadata is all there in either case.
>

I had tried that originally, but got an error:

bash-4.1$ pg_restore -L spc_restore_list.tmp -d spc_test_1
agency_backup.spc.2017.06.05_10.30.01.tar

pg_restore: [tar archiver] restoring data out of order is not supported in
this archive format: "10608.dat" is required, but comes before "10760.dat"
in the archive file.

The pg_dump doc page kinda suggests but doesn't quite say that you can't
re-order tar files; between that and the error message I gave up on that
possibility.  Are you suggesting it should work?

https://www.postgresql.org/docs/9.3/static/app-pgdump.html

The alternative archive file formats must be used with pg_restore
 to rebuild
the database. They allow pg_restore to be selective about what is restored,
or even to reorder the items prior to being restored. The archive file
formats are designed to be portable across architectures.

When used with one of the archive file formats and combined with pg_restore
, pg_dump provides a flexible archival and transfer mechanism. pg_dump can
be used to backup an entire database, then pg_restore can be used to
examine the archive and/or select which parts of the database are to be
restored. *The most flexible output file formats are the "custom" format
(-Fc) and the "directory" format(-Fd). They allow for selection and
reordering* of all archived items, support parallel restoration, and are
compressed by default. The "directory" format is the only format that
supports parallel dumps.
Cheers,
Ken
-- 
AGENCY Software
A Free Software data system
By and for non-profits
*http://agency-software.org/ *
*https://agency-software.org/demo/client
*
ken.tan...@agency-software.org
(253) 245-3801

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

Re: [GENERAL] Help with restoring a dump in Tar format? (dependencies/ordering)

[Tom Lane]

Ken Tanzer  writes:
> ...The rest of the DB is fine, but tbl_payment has 0 rows.  I believe this is
> because tbl_payment has a constraint that calls a function has_perm() that
> relies on data in a couple of other tables, and that tbl_payment is being
> restored before those tables.  I was able to created a new dump in Custom
> format, reorder the List file, and restore that successfully.

> So I can switch to Custom format for future backups.  But regarding the
> existing backups I have in Tar format, is there any way to successfully
> restore them?

FWIW, the business with making and editing a list file should work just
fine with a tar-format dump, not only with a custom-format dump.  The
metadata is all there in either case.

As already noted, it's hard to get pg_dump/pg_restore to cope
automatically with hidden dependencies like what you have here.
The fact that those other tables would need to be restored first
simply isn't visible to pg_dump.

regards, tom lane


-- 
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] Unable to install EASM postgre due to error 8023

[Scott Mead]

On Mon, Jun 5, 2017 at 9:59 PM, Garry Sim  wrote:

> Hi Scott,
>
>
>
> Is there a difference between postgre and Entrust Authority Security
> Manager Postgresql Database? But even if end of support, anyway of letting
> me have a better understanding of the error code ?
>

Ah, you're using the bundle that comes with EASM.  I would reach out to
their support for that error code.  It's not an error code that we in the
community would recognize, it's most likely well documented by their
support team.

--Scott




>
>
> *Regards, *
>
>
>
> *Garry Sim*
>
> Professional Service Consultant
>
>
>
> *NETRUST PTE LTD*
> 70 Bendemeer Road #05-03 Luzerne Singapore 339940
>
> DID: +65 6212 1393 <+65%206212%201393> | Fax +65 6212 1366
> <+65%206212%201366>  |Website http://www.netrust.net
>
>
>
>
>
>
>
> *From:* Scott Mead [mailto:sco...@openscg.com]
> *Sent:* Tuesday, June 6, 2017 9:55 AM
> *To:* Garry Sim 
> *Cc:* pgsql-general 
> *Subject:* Re: [GENERAL] Unable to install EASM postgre due to error 8023
>
>
>
>
>
>
>
> On Mon, Jun 5, 2017 at 6:14 AM, Garry Sim  wrote:
>
> Hi all,
>
>
>
> I did a search but unable to find anything in regards to this error. I am
> installing “01 SM_81SP1_Win_PostgreSQL_8323_setup.exe” but upon
> installing towards the ending, I am encountering this message. “Modifying
> the database files failed with an exit code of 8023”. I am currently
> installing at VM with OS: Windows Server 2012 R2
>
>
>
>
>
> Is that postgres version 8.3.23?  That went EOL in Feb 2013
>
>
>
> https://www.postgresql.org/support/versioning/
>
>
>
> --Scott
>
>
>
>
>
> *Regards, *
>
>
>
> *Garry Sim*
>
> Professional Service Consultant
>
>
>
>
> *NETRUST PTE LTD*70 Bendemeer Road #05-03 Luzerne Singapore 339940
>
> DID: +65 6212 1393 <+65%206212%201393> | Fax +65 6212 1366
> <+65%206212%201366>  |Website http://www.netrust.net
>
>
>
>
>
>
>
> --
>
> --
> Scott Mead
> Sr. Architect
> *OpenSCG *
>
> http://openscg.com
>



-- 
--
Scott Mead
Sr. Architect
*OpenSCG *
http://openscg.com

Re: [GENERAL] Unable to install EASM postgre due to error 8023

[Scott Mead]

On Mon, Jun 5, 2017 at 6:14 AM, Garry Sim  wrote:

> Hi all,
>
>
>
> I did a search but unable to find anything in regards to this error. I am
> installing “01 SM_81SP1_Win_PostgreSQL_8323_setup.exe” but upon
> installing towards the ending, I am encountering this message. “Modifying
> the database files failed with an exit code of 8023”. I am currently
> installing at VM with OS: Windows Server 2012 R2
>
>
>

Is that postgres version 8.3.23?  That went EOL in Feb 2013

https://www.postgresql.org/support/versioning/

--Scott


>
>
> *Regards, *
>
>
>
> *Garry Sim*
>
> Professional Service Consultant
>
>
>
> *NETRUST PTE LTD*
> 70 Bendemeer Road #05-03 Luzerne Singapore 339940
>
> DID: +65 6212 1393 <+65%206212%201393> | Fax +65 6212 1366
> <+65%206212%201366>  |Website http://www.netrust.net
>
>
>



-- 
--
Scott Mead
Sr. Architect
*OpenSCG *
http://openscg.com

[GENERAL] Unable to install EASM postgre due to error 8023

[Garry Sim]

Hi all,



I did a search but unable to find anything in regards to this error. I am
installing “01 SM_81SP1_Win_PostgreSQL_8323_setup.exe” but upon installing
towards the ending, I am encountering this message. “Modifying the database
files failed with an exit code of 8023”. I am currently installing at VM
with OS: Windows Server 2012 R2





*Regards, *



*Garry Sim*

Professional Service Consultant



*NETRUST PTE LTD*
70 Bendemeer Road #05-03 Luzerne Singapore 339940

DID: +65 6212 1393 | Fax +65 6212 1366  |Website http://www.netrust.net

Re: [GENERAL] Help with restoring a dump in Tar format? (dependencies/ordering)

[David G. Johnston]

On Mon, Jun 5, 2017 at 6:21 PM, Ken Tanzer  wrote:

> I do get the "make \d show relevant information" argument and that is one
>> that seems easier to solve...
>>
>
> Maybe I'm missing something, but I'm not sure how you'd solve this or
> change what \d shows for a table.  Right now I get to see this in my \d:
>
> "authorized_approvers_only" CHECK (approved_by IS NULL OR 
> has_perm(approved_by, 'APPROVE_PAYMENT'::character varying, 'W'::character
> varying))
>
> But when I move that to a trigger, I'll only see the trigger name.  Any
> while this procedure would be really short, others not so much, so you
> wouldn't really want to automatically display it inline.
>

​FWIW​

​I wouldn't show the trigger functions but I'd show something like:

CREATE ​trg_tbl2_exists_tbl3_missing_or_vice_versa
TRIGGER ON tbl1 CHANGES EXECUTE func_tbl1
REFERENCES tbl2 CHANGES EXECUTE func_tbl2
REFERENCES tbl3 CHANGES EXECUTE func_tbl3;

FOR tbl1
DEPENDS ON tbl2, tbl3 VIA TRIGGER
​trg_tbl2_exists_tbl3_missing_or_vice_versa

​FOR tbl2
DEPENDED ON BY tbl1 VIA TRIGGER ​​trg_tbl2_exists_tbl3_missing_or_vice_versa

FOR tbl3
DEPENDED ON BY tbl1 VIA TRIGGER ​​trg_tbl2_exists_tbl3_missing_or_vice_versa

I suspect the possibility to enforce that trigger execution doesn't touch
tables other than those specified.

​David J.

Re: [GENERAL] Help with restoring a dump in Tar format? (dependencies/ordering)

[Ken Tanzer]

>
> I do get the "make \d show relevant information" argument and that is one
> that seems easier to solve...
>

Maybe I'm missing something, but I'm not sure how you'd solve this or
change what \d shows for a table.  Right now I get to see this in my \d:

"authorized_approvers_only" CHECK (approved_by IS NULL OR
has_perm(approved_by, 'APPROVE_PAYMENT'::character varying,
'W'::character
varying))

But when I move that to a trigger, I'll only see the trigger name.  Any
while this procedure would be really short, others not so much, so you
wouldn't really want to automatically display it inline.

Ken

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

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

Re: [GENERAL] Help with restoring a dump in Tar format? (dependencies/ordering)

[David G. Johnston]

On Mon, Jun 5, 2017 at 5:59 PM, Ken Tanzer  wrote:

> I can't really make this an FK.  I can (and probably will) put this into a
>>> trigger.  Although it seems like an extra layer of wrapping just to call a
>>> function.  I'm curious if there's any conceptual reason why constraints
>>> couldn't (as an option) be restored after all the data is loaded, and
>>> whether there would be any negative consequences of that?  I could see if
>>> your data still didn't pass the CHECKs, it's already loaded.  But the
>>> constraint could then be marked not valid?
>>>
>>
>> Not sure why just know that if I stay within the guidelines it works, if
>> I do not its does not work:)
>>
>>
> That's fair enough, leaving aside the curiosity part.  Usually though the
> things you can't do just aren't allowed.  It's easier to overlook something
> that you shouldn't (but can) do!
>
>
​I find in life most things that are prohibited are actually doable -
you're just punished if you get caught doing them.  In all seriousness
though I agree it would be nice if that's how this worked; but decades of
historical precedent makes actual preventive enforcement ​difficult if not
impossible.

Since "test your backups" covers this potential problem, and so many
possible others, any non-trivial effort to solve the actual problem is hard
to justify spending time on.

I do get the "make \d show relevant information" argument and that is one
that seems easier to solve, since adding explicit dependencies during
trigger creation would be a purely new feature.

David J.

Re: [GENERAL] Help with restoring a dump in Tar format? (dependencies/ordering)

[Ken Tanzer]

>
> I can't really make this an FK.  I can (and probably will) put this into a
>> trigger.  Although it seems like an extra layer of wrapping just to call a
>> function.  I'm curious if there's any conceptual reason why constraints
>> couldn't (as an option) be restored after all the data is loaded, and
>> whether there would be any negative consequences of that?  I could see if
>> your data still didn't pass the CHECKs, it's already loaded.  But the
>> constraint could then be marked not valid?
>>
>
> Not sure why just know that if I stay within the guidelines it works, if I
> do not its does not work:)
>
>
That's fair enough, leaving aside the curiosity part.  Usually though the
things you can't do just aren't allowed.  It's easier to overlook something
that you shouldn't (but can) do!



> See that, but in your scenario you wanted to create a 'scratch' database
> so you are back to a user with privileges.


>
Yeah, I was thinking pg_dump could just conjure it up in the ether (and
then discard it), but I can see that doesn't really work.


Basically, if you have no way to test your backup/restore procedure before
> hand you are flying blind.
>
>
In this case, we had tested the restore part.  But then we changed the DB
in a way that made it stop working.  Good reminder to retest that
periodically!

Ken




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

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

Re: [GENERAL] Help with restoring a dump in Tar format? (dependencies/ordering)

[Adrian Klaver]

On 06/05/2017 05:15 PM, Ken Tanzer wrote:
Thanks Adrian and David.  That all makes sense, and I gather the answer 
regarding the existing dumps is "no, they can't be restored."  So be 
it.  Here's a couple of follow-on comments::


Ideally figure out how to write an actual FK constraint - otherwise
use triggers.


I can't really make this an FK.  I can (and probably will) put this into 
a trigger.  Although it seems like an extra layer of wrapping just to 
call a function.  I'm curious if there's any conceptual reason why 
constraints couldn't (as an option) be restored after all the data is 
loaded, and whether there would be any negative consequences of that?  I 
could see if your data still didn't pass the CHECKs, it's already 
loaded.  But the constraint could then be marked not valid?


Not sure why just know that if I stay within the guidelines it works, if 
I do not its does not work:)





-1; pg_dump should not be trying to restore things.​  The core
developers shouldn't really concern themselves with the various and
sundry ways people might want to setup such a process.  You have
tools for dump, and tools for restore, and you can combine them in
whatever fashion you deem useful.  Or otherwise acquire someone
else's ideas.


I get that as a general principle.  OTOH, being able to restore your 
backups isn't just a random or inconsequential feature.  I have access 
to the superuser and can create DBs, but users in more locked down 
scenarios might not be able to do so.




See that, but in your scenario you wanted to create a 'scratch' database 
so you are back to a user with privileges.  Then there is the whole 
overhead of doing a restore twice. Basically, if you have no way to test 
your backup/restore procedure before hand you are flying blind.



--
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] Help with restoring a dump in Tar format? (dependencies/ordering)

[John R Pierce]

On 6/5/2017 5:49 PM, David G. Johnston wrote:
On Mon, Jun 5, 2017 at 5:40 PM, John R Pierce >wrote:


​i​
ndeed, any sort of constraint that invokes a function call which
looks at other tables could later be invalidated if those other
tables change, and postgres would be none the smarter.   the same
goes for trigger based checks.


​ Yes.  I could imagine a new kind of "multi-referential trigger" that 
would specify all relations it touches and the function to fire when 
each of them is updated.  While you'd still have to write the 
functions correctly it would at least allow one to explicitly model 
the multi-table dynamic in pg_catalog.  Lacking that CHECK is no worse 
than TRIGGER and we've decided to say "use triggers".



at $job, the policy is, AVOID ALL TRIGGERS AND FANCY CONSTRAINTS :)

they don't even like using foreign key references, and rely on code 
logic to do most joins in the performance-critical OLTP side of things.



--
john r pierce, recycling bits in santa cruz

Re: [GENERAL] Help with restoring a dump in Tar format? (dependencies/ordering)

[David G. Johnston]

On Mon, Jun 5, 2017 at 5:40 PM, John R Pierce  wrote:

> ​i​
> ndeed, any sort of constraint that invokes a function call which looks at
> other tables could later be invalidated if those other tables change, and
> postgres would be none the smarter.   the same goes for trigger based
> checks.
>

​Yes.  I could imagine a new kind of "multi-referential trigger" that would
specify all relations it touches and the function to fire when each of them
is updated.  While you'd still have to write the functions correctly it
would at least allow one to explicitly model the multi-table dynamic in
pg_catalog.  Lacking that CHECK is no worse than TRIGGER and we've decided
to say "use triggers".

David J.​

Re: [GENERAL] Help with restoring a dump in Tar format? (dependencies/ordering)

[Ken Tanzer]

>
> Aside from being a bit more verbose there is nothing useful that writing
> this as "CHECK function()" provides that you don't also get by writing
> "CREATE TRIGGER".
>

I agree you get the same result.  It may be a minor issue, but for me it is
convenient to see the logic spelled out when using \d on the table.

Cheers,
Ken

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

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

Re: [GENERAL] Help with restoring a dump in Tar format? (dependencies/ordering)

[John R Pierce]

On 6/5/2017 5:32 PM, David G. Johnston wrote:
On Mon, Jun 5, 2017 at 5:15 PM, Ken Tanzer >wrote:


From the docs:
https://www.postgresql.org/docs/9.6/static/sql-createtable.html

"Currently, CHECK expressions cannot contain subqueries nor
refer to variables other than columns of the current row. The
system column tableoid may be referenced, but not any other
system column.


I wonder if that should say "should not," or be followed by
something like this:


Make it say "must not" and I'd agree to change the word "cannot" and 
leave the rest.  Adding a note regarding functions seems appropriate.


Aside from being a bit more verbose there is nothing useful that 
writing this as "CHECK function()" provides that you don't also get by 
writing "CREATE TRIGGER". In a green field we'd probably lock down 
CHECK a bit more but there is too much code that is technically wrong 
but correctly functioning that we don't want to break.  IOW, we cannot 
mandate that the supplied function be immutable even though we 
should.  And we don't even enforce immutable execution if a function 
is defined that way.



indeed, any sort of constraint that invokes a function call which looks 
at other tables could later be invalidated if those other tables change, 
and postgres would be none the smarter.   the same goes for trigger 
based checks.




--
john r pierce, recycling bits in santa cruz

Re: [GENERAL] Help with restoring a dump in Tar format? (dependencies/ordering)

[David G. Johnston]

On Mon, Jun 5, 2017 at 5:15 PM, Ken Tanzer  wrote:

> From the docs:
>> https://www.postgresql.org/docs/9.6/static/sql-createtable.html
>> "Currently, CHECK expressions cannot contain subqueries nor refer to
>> variables other than columns of the current row. The system column tableoid
>> may be referenced, but not any other system column.
>
>
> I wonder if that should say "should not," or be followed by something like
> this:
>
>
Make it say "must not" and I'd agree to change the word "cannot" and leave
the rest.  Adding a note regarding functions seems appropriate.

Aside from being a bit more verbose there is nothing useful that writing
this as "CHECK function()" provides that you don't also get by writing
"CREATE TRIGGER". In a green field we'd probably lock down CHECK a bit more
but there is too much code that is technically wrong but correctly
functioning that we don't want to break.  IOW, we cannot mandate that the
supplied function be immutable even though we should.  And we don't even
enforce immutable execution if a function is defined that way.

​David J.​

Re: [GENERAL] Help with restoring a dump in Tar format? (dependencies/ordering)

[John R Pierce]

On 6/5/2017 5:15 PM, Ken Tanzer wrote:
I can't really make this an FK.  I can (and probably will) put this 
into a trigger.  Although it seems like an extra layer of wrapping 
just to call a function.  I'm curious if there's any conceptual reason 
why constraints couldn't (as an option) be restored after all the data 
is loaded, and whether there would be any negative consequences of 
that?  I could see if your data still didn't pass the CHECKs, it's 
already loaded.  But the constraint could then be marked not valid?



when you have constraints that rely on calling functions, how would it 
know what order to check things in ?



--
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] Help with restoring a dump in Tar format? (dependencies/ordering)

[Ken Tanzer]

Thanks Adrian and David.  That all makes sense, and I gather the answer
regarding the existing dumps is "no, they can't be restored."  So be it.
Here's a couple of follow-on comments::

Ideally figure out how to write an actual FK constraint - otherwise use
> triggers.


I can't really make this an FK.  I can (and probably will) put this into a
trigger.  Although it seems like an extra layer of wrapping just to call a
function.  I'm curious if there's any conceptual reason why constraints
couldn't (as an option) be restored after all the data is loaded, and
whether there would be any negative consequences of that?  I could see if
your data still didn't pass the CHECKs, it's already loaded.  But the
constraint could then be marked not valid?


-1; pg_dump should not be trying to restore things.​  The core developers
> shouldn't really concern themselves with the various and sundry ways people
> might want to setup such a process.  You have tools for dump, and tools for
> restore, and you can combine them in whatever fashion you deem useful.  Or
> otherwise acquire someone else's ideas.


I get that as a general principle.  OTOH, being able to restore your
backups isn't just a random or inconsequential feature.  I have access to
the superuser and can create DBs, but users in more locked down scenarios
might not be able to do so.


>From the docs:
> https://www.postgresql.org/docs/9.6/static/sql-createtable.html
> "Currently, CHECK expressions cannot contain subqueries nor refer to
> variables other than columns of the current row. The system column tableoid
> may be referenced, but not any other system column.


I wonder if that should say "should not," or be followed by something like
this:

n.b., In CHECK expressions, Postgres will not prevent you from calling
functions that reference other rows or tables.  However, doing so may have
undesirable consequences, including the possible inability to restore from
output created by pg_dump.

(Are there other possible pitfalls too, or is that the only one?)

Cheers,
Ken


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

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

Re: [GENERAL] Redo the filenode link in tablespace

[Adrian Klaver]

On 06/05/2017 04:07 PM, tel medola wrote:

Hi,
attachment.

select:
select * from pg_class where relfilenode = 5214489


Next, what do you get from:

In psql
=> \x
Expanded display is on.

select oid, * from pg_class where oid = 5214493;

No need to create an attachment, just cut and paste into message.




Thanks

2017-06-05 16:02 GMT-03:00 Adrian Klaver >:


On 06/05/2017 11:55 AM, tel medola wrote:

show?
/Yes/


Yes, what?

Please run the command as shown:

select * from pg_class where relfilenode = 5214489;

and show the result.


Do not delete the pg_statistic table. I would not even delete
from it.
/Sorry, I already deleted it. I looked for something official
and found in the site in postgres the reference that the delete
could be done
(https://www.postgresql.org/docs/9.1/static/release-7-4-2.html
)
and I ended up doing it. Now I'm running a vacuum analyze across
the base./


2017-06-05 15:41 GMT-03:00 Adrian Klaver

>>:


 On 06/05/2017 07:17 AM, tel medola wrote:

 Done.
 I followed the steps below after reconnecting the filenode:

 Select * from "05122016".repositorio
 Count -> 0

 Then, I execute the commands:
 Analyze "05122016".repositorio;
 Count -> 0
 Reindex "05122016".repositorio;
 Count -> 1509046
 Yes!

 But...


 select * from "05122016".repository where id_documento =
 '{A647885D-9738-4617-ACE6-6351F6ADD341}'

 Returns the error below:
 Missing chunk number 0 for toast value 10259186 in
pg_toast_9277966


 What does:

 select * from pg_class where relfilenode = 5214489;

 show?



 I read in the forum to run the command:
 Delete from pg_statistic;
 Reindex table pg_statistic;
 Vacuum analyze;

 Is it okay to delete the pg_statistic table?


 Do not delete the pg_statistic table. I would not even
delete from it.

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




-- 
Adrian Klaver

adrian.kla...@aklaver.com 





--
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] Redo the filenode link in tablespace

[tel medola]

Hi,
attachment.

select:
select * from pg_class where relfilenode = 5214489

Thanks

2017-06-05 16:02 GMT-03:00 Adrian Klaver :

> On 06/05/2017 11:55 AM, tel medola wrote:
>
>> show?
>> /Yes/
>>
>
> Yes, what?
>
> Please run the command as shown:
>
> select * from pg_class where relfilenode = 5214489;
>
> and show the result.
>
>
>> Do not delete the pg_statistic table. I would not even delete from it.
>> /Sorry, I already deleted it. I looked for something official and found
>> in the site in postgres the reference that the delete could be done (
>> https://www.postgresql.org/docs/9.1/static/release-7-4-2.html) and I
>> ended up doing it. Now I'm running a vacuum analyze across the base./
>>
>>
>> 2017-06-05 15:41 GMT-03:00 Adrian Klaver > >:
>>
>>
>> On 06/05/2017 07:17 AM, tel medola wrote:
>>
>> Done.
>> I followed the steps below after reconnecting the filenode:
>>
>> Select * from "05122016".repositorio
>> Count -> 0
>>
>> Then, I execute the commands:
>> Analyze "05122016".repositorio;
>> Count -> 0
>> Reindex "05122016".repositorio;
>> Count -> 1509046
>> Yes!
>>
>> But...
>>
>>
>> select * from "05122016".repository where id_documento =
>> '{A647885D-9738-4617-ACE6-6351F6ADD341}'
>>
>> Returns the error below:
>> Missing chunk number 0 for toast value 10259186 in
>> pg_toast_9277966
>>
>>
>> What does:
>>
>> select * from pg_class where relfilenode = 5214489;
>>
>> show?
>>
>>
>>
>> I read in the forum to run the command:
>> Delete from pg_statistic;
>> Reindex table pg_statistic;
>> Vacuum analyze;
>>
>> Is it okay to delete the pg_statistic table?
>>
>>
>> Do not delete the pg_statistic table. I would not even delete from it.
>>
>> -- Adrian Klaver
>> adrian.kla...@aklaver.com 
>>
>>
>>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>
   relname   | relnamespace | reltype | reloftype | relowner | relam | 
relfilenode | reltablespace | relpages |  reltuples   | relallvisible | 
reltoastrelid | reltoastidxid | relhasindex | relisshared | relpersistence | 
relkind | relnatts | relchecks | relhasoids | relhaspkey | relhasrules | 
relhastriggers | relhassubclass | relispopulated | relfrozenxid | relminmxid | 
relacl | reloptions 
-+--+-+---+--+---+-+---+--+--+---+---+---+-+-++-+--+---+++-++++--+++
 repositorio |  5205962 | 5214491 | 0 |   10 | 0 | 
5214489 |   5205910 |79303 | 1.31566e+006 | 79303 |   
5214493 | 0 | t   | f   | p  | r   
|7 | 0 | f  | t  | f   | f  
| f  | t  |  9360288 |  1 || 
(1 registro)


-- 
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] Help with restoring a dump in Tar format? (dependencies/ordering)

[Adrian Klaver]

On 06/05/2017 03:35 PM, Ken Tanzer wrote:

On 9.3.17, I tried to restore a tar from pg_dump.  It failed thusly:

bash-4.1$ pg_restore -d spc_test_1 agency_backup.spc.2017.06.05_10.30.01.tar

pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 10608; 0 107743 TABLE 
DATA tbl_payment spc
pg_restore: [archiver (db)] COPY failed for table "tbl_payment": ERROR: 
  new row for relation "tbl_payment" violates check constraint 
"authorized_approvers_only"
DETAIL:  Failing row contains (286541, 3685, 2015-09-14, ADJUST, null, 
null, 137798, 93.00, HONEY, 4841, 2, SHONCRE, September adjustment, 
2015-10-01, null, null, null, null, null, f, f, t, f, f, f, f, null, 
null, null, null, 6, 2015-09-14 16:43:37, 25, 2016-02-08 16:34:20, f, 
null, null, null, Adjusting approved_at to changed_at for first few 
approvals

, 6, 2015-09-14 16:43:37, 2015-09-17).
CONTEXT:  COPY tbl_payment, line 179785: "2865413685   
  2015-09-14  ADJUST  \N  \N  137798  93.00   HONEY   48412

 SHONCRE September adjustment2015-10-0..."
WARNING: errors ignored on restore: 1

The rest of the DB is fine, but tbl_payment has 0 rows.  I believe this 
is because tbl_payment has a constraint that calls a function has_perm() 
that relies on data in a couple of other tables, and that tbl_payment is 
being restored before those tables.  I was able to created a new dump in 
Custom format, reorder the List file, and restore that successfully.


See this thread for more info:
https://www.postgresql.org/message-id/alpine.DEB.2.20.1703311620581.12863%40tglase.lan.tarent.de

From the docs:

https://www.postgresql.org/docs/9.6/static/sql-createtable.html

"Currently, CHECK expressions cannot contain subqueries nor refer to 
variables other than columns of the current row. The system column 
tableoid may be referenced, but not any other system column.




So I can switch to Custom format for future backups.  But regarding the 
existing backups I have in Tar format, is there any way to successfully 
restore them?  Specifically:


  * Any way to ignore or delay constraint checking?  Something like
disable-triggers?

  * Any way to tell pg_restore to skip past the failing row, and restore
the rest of what was in tbl_payment?

  * Some other way to go about this?


Change the check constraint to a trigger.



I also wonder if you folks might consider adding something like a 
--test_restore option to pg_dump that would attempt to create a new 
(scratch) DB from the output it creates, and report any errors?  I know 


Not that I know of. It would be easy enough to point pg_restore at your 
own scratch database for testing purposes.


the pieces are all there for us users to do that ourselves, but it would 
be handy for automated backups and might help us to avoid creating 
backups that won't restore successfully.  In my case, I think the 
problem started from changes we made about 9 months ago, and happily I 
discovered it during development/testing and not after a DB crash, which 
is why I'm also happily not gouging my eyeballs out right now. :)


Cheers, and thanks in advance!

Ken


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


(253) 245-3801

Subscribe to the mailing list 
 to

learn more about AGENCY or
follow the discussion.



--
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] Help with restoring a dump in Tar format? (dependencies/ordering)

[David G. Johnston]

On Mon, Jun 5, 2017 at 3:35 PM, Ken Tanzer  wrote:

> I believe this is because tbl_payment has a constraint that calls a
> function has_perm() that relies on data in a couple of other tables
>

​Indeed this is the cause.  That configuration is not supported.  If you
need to lookup values in other tables you either need to use an actual FK
constraint or create a trigger for the validation.


> So I can switch to Custom format for future backups.  But regarding the
> existing backups I have in Tar format, is there any way to successfully
> restore them?  Specifically:
>
>- Any way to ignore or delay constraint checking?  Something like
>disable-triggers?
>
> ​Using and then disabling triggers is the "closest" solution​.

>
>- Any way to tell pg_restore to skip past the failing row, and restore
>the rest of what was in tbl_payment?
>
> ​No, COPY doesn't have that capability and that is what is being used
under the hood.

>
>- Some other way to go about this?
>
> ​Ideally figure out how to write an actual FK constraint - otherwise use
triggers.​


> I also wonder if you folks might consider adding something like a
> --test_restore option to pg_dump
>

-1; pg_dump should not be trying to restore things.​  The core developers
shouldn't really concern themselves with the various and sundry ways people
might want to setup such a process.  You have tools for dump, and tools for
restore, and you can combine them in whatever fashion you deem useful.  Or
otherwise acquire someone else's ideas.

​David J.​

[GENERAL] Help with restoring a dump in Tar format? (dependencies/ordering)

[Ken Tanzer]

On 9.3.17, I tried to restore a tar from pg_dump.  It failed thusly:

bash-4.1$ pg_restore -d spc_test_1 agency_backup.spc.2017.06.05_10.30.01.tar

pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 10608; 0 107743 TABLE DATA
tbl_payment spc
pg_restore: [archiver (db)] COPY failed for table "tbl_payment": ERROR:
 new row for relation "tbl_payment" violates check constraint
"authorized_approvers_only"
DETAIL:  Failing row contains (286541, 3685, 2015-09-14, ADJUST, null,
null, 137798, 93.00, HONEY, 4841, 2, SHONCRE, September adjustment,
2015-10-01, null, null, null, null, null, f, f, t, f, f, f, f, null, null,
null, null, 6, 2015-09-14 16:43:37, 25, 2016-02-08 16:34:20, f, null, null,
null, Adjusting approved_at to changed_at for first few approvals
, 6, 2015-09-14 16:43:37, 2015-09-17).
CONTEXT:  COPY tbl_payment, line 179785: "28654136852015-09-14
 ADJUST  \N  \N  137798  93.00   HONEY   48412
SHONCRE September adjustment2015-10-0..."
WARNING: errors ignored on restore: 1

The rest of the DB is fine, but tbl_payment has 0 rows.  I believe this is
because tbl_payment has a constraint that calls a function has_perm() that
relies on data in a couple of other tables, and that tbl_payment is being
restored before those tables.  I was able to created a new dump in Custom
format, reorder the List file, and restore that successfully.

So I can switch to Custom format for future backups.  But regarding the
existing backups I have in Tar format, is there any way to successfully
restore them?  Specifically:

   - Any way to ignore or delay constraint checking?  Something like
   disable-triggers?


   - Any way to tell pg_restore to skip past the failing row, and restore
   the rest of what was in tbl_payment?


   - Some other way to go about this?

I also wonder if you folks might consider adding something like a
--test_restore option to pg_dump that would attempt to create a new
(scratch) DB from the output it creates, and report any errors?  I know the
pieces are all there for us users to do that ourselves, but it would be
handy for automated backups and might help us to avoid creating backups
that won't restore successfully.  In my case, I think the problem started
from changes we made about 9 months ago, and happily I discovered it during
development/testing and not after a DB crash, which is why I'm also happily
not gouging my eyeballs out right now. :)

Cheers, and thanks in advance!

Ken


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

(253) 245-3801

Subscribe to the mailing list

 to
learn more about AGENCY or
follow the discussion.

Re: [GENERAL] Redo the filenode link in tablespace

[tel medola]

I will send as soon as possible. He's running the vacuum yet.

2017-06-05 16:02 GMT-03:00 Adrian Klaver :

> On 06/05/2017 11:55 AM, tel medola wrote:
>
>> show?
>> /Yes/
>>
>
> Yes, what?
>
> Please run the command as shown:
>
> select * from pg_class where relfilenode = 5214489;
>
> and show the result.
>
>
>> Do not delete the pg_statistic table. I would not even delete from it.
>> /Sorry, I already deleted it. I looked for something official and found
>> in the site in postgres the reference that the delete could be done (
>> https://www.postgresql.org/docs/9.1/static/release-7-4-2.html) and I
>> ended up doing it. Now I'm running a vacuum analyze across the base./
>>
>>
>> 2017-06-05 15:41 GMT-03:00 Adrian Klaver > >:
>>
>>
>> On 06/05/2017 07:17 AM, tel medola wrote:
>>
>> Done.
>> I followed the steps below after reconnecting the filenode:
>>
>> Select * from "05122016".repositorio
>> Count -> 0
>>
>> Then, I execute the commands:
>> Analyze "05122016".repositorio;
>> Count -> 0
>> Reindex "05122016".repositorio;
>> Count -> 1509046
>> Yes!
>>
>> But...
>>
>>
>> select * from "05122016".repository where id_documento =
>> '{A647885D-9738-4617-ACE6-6351F6ADD341}'
>>
>> Returns the error below:
>> Missing chunk number 0 for toast value 10259186 in
>> pg_toast_9277966
>>
>>
>> What does:
>>
>> select * from pg_class where relfilenode = 5214489;
>>
>> show?
>>
>>
>>
>> I read in the forum to run the command:
>> Delete from pg_statistic;
>> Reindex table pg_statistic;
>> Vacuum analyze;
>>
>> Is it okay to delete the pg_statistic table?
>>
>>
>> Do not delete the pg_statistic table. I would not even delete from it.
>>
>> -- Adrian Klaver
>> adrian.kla...@aklaver.com 
>>
>>
>>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Re: [GENERAL] Redo the filenode link in tablespace

[Adrian Klaver]

On 06/05/2017 11:55 AM, tel medola wrote:

show?
/Yes/


Yes, what?

Please run the command as shown:

select * from pg_class where relfilenode = 5214489;

and show the result.



Do not delete the pg_statistic table. I would not even delete from it.
/Sorry, I already deleted it. I looked for something official and found 
in the site in postgres the reference that the delete could be done 
(https://www.postgresql.org/docs/9.1/static/release-7-4-2.html) and I 
ended up doing it. Now I'm running a vacuum analyze across the base./



2017-06-05 15:41 GMT-03:00 Adrian Klaver >:


On 06/05/2017 07:17 AM, tel medola wrote:

Done.
I followed the steps below after reconnecting the filenode:

Select * from "05122016".repositorio
Count -> 0

Then, I execute the commands:
Analyze "05122016".repositorio;
Count -> 0
Reindex "05122016".repositorio;
Count -> 1509046
Yes!

But...


select * from "05122016".repository where id_documento =
'{A647885D-9738-4617-ACE6-6351F6ADD341}'

Returns the error below:
Missing chunk number 0 for toast value 10259186 in pg_toast_9277966


What does:

select * from pg_class where relfilenode = 5214489;

show?



I read in the forum to run the command:
Delete from pg_statistic;
Reindex table pg_statistic;
Vacuum analyze;

Is it okay to delete the pg_statistic table?


Do not delete the pg_statistic table. I would not even delete from it.

-- 
Adrian Klaver

adrian.kla...@aklaver.com 





--
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] Redo the filenode link in tablespace

[tel medola]

show?
*Yes*

Do not delete the pg_statistic table. I would not even delete from it.
*Sorry, I already deleted it. I looked for something official and found in
the site in postgres the reference that the delete could be done
(https://www.postgresql.org/docs/9.1/static/release-7-4-2.html
) and I
ended up doing it. Now I'm running a vacuum analyze across the base.*


2017-06-05 15:41 GMT-03:00 Adrian Klaver :

> On 06/05/2017 07:17 AM, tel medola wrote:
>
>> Done.
>> I followed the steps below after reconnecting the filenode:
>>
>> Select * from "05122016".repositorio
>> Count -> 0
>>
>> Then, I execute the commands:
>> Analyze "05122016".repositorio;
>> Count -> 0
>> Reindex "05122016".repositorio;
>> Count -> 1509046
>> Yes!
>>
>> But...
>>
>>
>> select * from "05122016".repository where id_documento =
>> '{A647885D-9738-4617-ACE6-6351F6ADD341}'
>>
>> Returns the error below:
>> Missing chunk number 0 for toast value 10259186 in pg_toast_9277966
>>
>
> What does:
>
> select * from pg_class where relfilenode = 5214489;
>
> show?
>
>
>
>> I read in the forum to run the command:
>> Delete from pg_statistic;
>> Reindex table pg_statistic;
>> Vacuum analyze;
>>
>> Is it okay to delete the pg_statistic table?
>>
>>
> Do not delete the pg_statistic table. I would not even delete from it.
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Re: [GENERAL] Redo the filenode link in tablespace

[Adrian Klaver]

On 06/05/2017 07:17 AM, tel medola wrote:

Done.
I followed the steps below after reconnecting the filenode:

Select * from "05122016".repositorio
Count -> 0

Then, I execute the commands:
Analyze "05122016".repositorio;
Count -> 0
Reindex "05122016".repositorio;
Count -> 1509046
Yes!

But...


select * from "05122016".repository where id_documento = 
'{A647885D-9738-4617-ACE6-6351F6ADD341}'


Returns the error below:
Missing chunk number 0 for toast value 10259186 in pg_toast_9277966


What does:

select * from pg_class where relfilenode = 5214489;

show?




I read in the forum to run the command:
Delete from pg_statistic;
Reindex table pg_statistic;
Vacuum analyze;

Is it okay to delete the pg_statistic table?



Do not delete the pg_statistic table. I would not even delete from 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] Redo the filenode link in tablespace

[tel medola]

Done.
I followed the steps below after reconnecting the filenode:

Select * from "05122016".repositorio
Count -> 0

Then, I execute the commands:
Analyze "05122016".repositorio;
Count -> 0
Reindex "05122016".repositorio;
Count -> 1509046
Yes!

But...


select * from "05122016".repository where id_documento =
'{A647885D-9738-4617-ACE6-6351F6ADD341}'

Returns the error below:
Missing chunk number 0 for toast value 10259186 in pg_toast_9277966

I tried to do the commands below, but the error persists:
REINDEX table pg_toast.pg_toast_9277966;
VACUUM ANALYZE pg_toast_9277966
VACUUM ANALYZE "05122016".repositorio;

REINDEX TABLE pg_statistic;

I read in the forum to run the command:
Delete from pg_statistic;
Reindex table pg_statistic;
Vacuum analyze;

Is it okay to delete the pg_statistic table?

2017-06-05 9:24 GMT-03:00 tel medola :

> Sorry. I got sick these days and could not read my emails.
>
> Thanks for your help.
> I'll try to point to the direct node and see what happens.
>
> 2017-06-01 10:29 GMT-03:00 Adrian Klaver :
>
>> On 06/01/2017 03:47 AM, tel medola wrote:
>>
>>> Did you get any help with this?
>>> /I formatted correctly before sending the email. Maybe you should ask
>>> yourself if the mail server did not remove the formatting./
>>>
>>
>> I was talking about help with your relfilenode issue, I learned to deal
>> with the formatting awhile ago.
>>
>> /
>>> /
>>> Well the relpages, reltuples are estimated values that can be updated
>>> with an ANALYZE./
>>> /
>>>
>>> /I can not make analyze on a table whose filenode is pointing to another
>>> reference. The table is empty, just because the filenode does not point to
>>> the correct ID./
>>>
>>
>> Understood, I was just saying that if you could get the table pointing at
>> the right relfilenode the other values would be synced up with an ANALYZE.
>>
>> At this point it is time to just try something. You have file level
>> backups of the various backups, so you can restore that if something goes
>> wrong, correct?
>>
>> For 01052016.repositorio with current pg_class entry of relfilenode of
>> 13741352, change that back to the old entry of 5214489.
>>
>>
>>
>>
>>
>> --
>> Adrian Klaver
>> adrian.kla...@aklaver.com
>>
>
>

Re: [GENERAL] Redo the filenode link in tablespace

[tel medola]

Sorry. I got sick these days and could not read my emails.

Thanks for your help.
I'll try to point to the direct node and see what happens.

2017-06-01 10:29 GMT-03:00 Adrian Klaver :

> On 06/01/2017 03:47 AM, tel medola wrote:
>
>> Did you get any help with this?
>> /I formatted correctly before sending the email. Maybe you should ask
>> yourself if the mail server did not remove the formatting./
>>
>
> I was talking about help with your relfilenode issue, I learned to deal
> with the formatting awhile ago.
>
> /
>> /
>> Well the relpages, reltuples are estimated values that can be updated
>> with an ANALYZE./
>> /
>>
>> /I can not make analyze on a table whose filenode is pointing to another
>> reference. The table is empty, just because the filenode does not point to
>> the correct ID./
>>
>
> Understood, I was just saying that if you could get the table pointing at
> the right relfilenode the other values would be synced up with an ANALYZE.
>
> At this point it is time to just try something. You have file level
> backups of the various backups, so you can restore that if something goes
> wrong, correct?
>
> For 01052016.repositorio with current pg_class entry of relfilenode of
> 13741352, change that back to the old entry of 5214489.
>
>
>
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Re: [GENERAL] Oracle database into PostgreSQL using Ora2PG tool.

[PAWAN SHARMA]

On Sat, Jun 3, 2017 at 1:48 AM, Igor Neyman  wrote:

>
> I wonder, does plpgsql compilation check for existence of the
> add_job_history function or is that a runtime check?
>
> 
>
> At runtime.
>
>

Hi Neyman,

Thanks it's done by adding PERFORM.

DROP TRIGGER IF EXISTS update_job_history ON employees CASCADE;

CREATE OR REPLACE FUNCTION trigger_fct_update_job_history() RETURNS trigger
AS $BODY$

BEGIN

PERFORM  add_job_history(OLD.employee_id, OLD.hire_date, LOCALTIMESTAMP,

  OLD.job_id, OLD.department_id);

RETURN NEW;

END

$BODY$

 LANGUAGE 'plpgsql';