Re: pg_dump order of operation
Ron writes: > In v8.4, I noticed that the tables seemed to be dumped in alphabetical > order. Not so much, though, in a multithreaded 9.6 dump of an 8.4 database; > there's no pattern that I can discern. > In what order does the 9.6 pg_dump dump tables? I don't believe the ordering rules have changed materially since 8.4; it's intended to be by object kind, and within that by name, except where dependencies force doing something else. But what are you thinking constitutes the "dump order" in a parallel dump? regards, tom lane
"cache lookup failed for type ####" when running unit tests
Hi all, I have a web application with a bunch of unit tests which involve accessing a PostgreSQL database. My schema contains composite types and stored procedures. When running the tests, I'm getting a "com.impossibl.postgres.jdbc.PGSQLSimpleException: cache lookup failed for type 64790" when trying to call a stored procedure whose parameter is an array of a composite type (e.g. "create function foo(param comp_type[]") The funny thing is, when I run the specific test in isolation the problem disappears. Note that the tests are grouped into fixtures and each fixture resets the database when run (it drops all tables, types, and functions then recreates them) and they are run serially (so it's not a parallel execution problem). Also, each test is run inside a transaction that's set to rollback after the test is finished in order to reduce the amount database resets. I'm having problems understanding what's causing the problem but i suspect that the rapid database resets paired with transaction use (abuse?) are the ones causing it. I've tried delays but those don't fix the problems.
Re: pg_dump order of operation
On 08/26/2018 10:24 AM, Tom Lane wrote: Ron writes: In v8.4, I noticed that the tables seemed to be dumped in alphabetical order. Not so much, though, in a multithreaded 9.6 dump of an 8.4 database; there's no pattern that I can discern. In what order does the 9.6 pg_dump dump tables? I don't believe the ordering rules have changed materially since 8.4; it's intended to be by object kind, and within that by name, except where dependencies force doing something else. I thought that didn't matter, since FK and PK constraints were added (in the required order) after all data was loaded). But what are you thinking constitutes the "dump order" in a parallel dump? I don't understand your question. -- Angular momentum makes the world go 'round.
Re: Join condition parsing puzzle
On Thu, Aug 23, 2018 at 4:51 PM, Tom Lane wrote: > > Mark Jeffcoat writes: > > I would have claimed before seeing this example that it wasn't even > > grammatical; I thought the only legal place to write the ON clause was > > immediately after the JOIN. Apparently not. > > You read it as > > SELECT * > FROM > relation_a > LEFT JOIN (relation_b > JOIN relation_c > ON (relation_c.id_p = relation_b.id_p)) > ON (relation_a.id_c = relation_b.id_c AND relation_a.id_v = > relation_b.id_v); > > There's no other valid way to parenthesize it, so that's what > the parser does. Thank you very much for your help, Tom. In retrospect, I see I'd over-generalized the rule that sub-selects in the from clause require an alias. Clear now. -- Mark Jeffcoat Austin, TX
Re: pg_sample
Sir i'm getting error in both commands [root@ip-88-8-8-17 ~]# perl -MCPAN -e 'install Bundle::DBI' Can't locate CPAN.pm in @INC (@INC contains: /usr/local/lib64/perl5 /usr/local/share/perl5 /usr/lib64/perl5/vendor_perl /usr/share/perl5/vendor_perl /usr/lib64/perl5 /usr/share/perl5 .). BEGIN failed--compilation aborted. [root@ip-88-8-8-17 ~]# perl -MCPAN -e 'install DBD::Pg' Can't locate CPAN.pm in @INC (@INC contains: /usr/local/lib64/perl5 /usr/local/share/perl5 /usr/lib64/perl5/vendor_perl /usr/share/perl5/vendor_perl /usr/lib64/perl5 /usr/share/perl5 .). BEGIN failed--compilation aborted. operating system =CentOS Linux release 7.5.1804 (Core) thanks On Fri, Aug 24, 2018 at 6:16 PM, Abhinav Mehta wrote: > Solution, execute this on your linux terminal - > > $ perl -MCPAN -e 'install Bundle::DBI' > $ perl -MCPAN -e 'install DBD::Pg' > > > On 24-Aug-2018, at 6:13 PM, Ravi Krishna wrote: > > > >> > >> sir have taken pg_sample > >> Now i want to run pg_sample with credential but i'm getting this error > >> > >> Can't locate DBI.pm in @INC (@INC contains: /usr/local/lib64/perl5 > /usr/local/share/perl5 /usr/lib64/perl5/vendor_perl > /usr/share/perl5/vendor_perl /usr/lib64/perl5 /usr/share/perl5 .) at > ./pg_sample line 192. > >> BEGIN failed--compilation aborted at ./pg_sample line 192. > > > > As is clear from the message, you need to install Perl DBI/DBD first. > > > > > > -- -- With Regards Naveen Dabas Ph. 9017298370 -- *Important Disclaimer:* Information contained in this email is for the recipient primarily addressed to. If you are not the primary recipient or are not supposed to receive this email, you are advised to kindly delete the email or the thread and notify of the error. The logo is a registered and copyrighted property of *ACTAS TECHNOLOGIES PRIVATE LIMITED*. Do not use it without authorization.
Re: "cache lookup failed for type ####" when running unit tests
Wilhansen Li writes: > I have a web application with a bunch of unit tests which involve accessing > a PostgreSQL database. My schema contains composite types and stored > procedures. > When running the tests, I'm getting a > "com.impossibl.postgres.jdbc.PGSQLSimpleException: cache lookup failed for > type 64790" when trying to call a stored procedure whose parameter is an > array of a composite type (e.g. "create function foo(param comp_type[]") > The funny thing is, when I run the specific test in isolation the problem > disappears. Hard to be sure when you haven't shown us any code, but I suspect the issue boils down to caching of type data inside a plpgsql function that *uses* some type you dropped and recreated, without having any parameter of that type. (If it did have such a parameter, you'd have been forced to drop and recreate the function, eliminating the cached info. That doesn't apply though to internal uses, such as a variable of the type.) Another possibility, if you're trying to pass an argument of such a type directly from the client side, is that the JDBC driver is caching data about that type name and doesn't realize you've replaced it with some new definition. regards, tom lane
Re: pg_dump order of operation
Ron writes: > On 08/26/2018 10:24 AM, Tom Lane wrote: >> Ron writes: >>> In what order does the 9.6 pg_dump dump tables? >> I don't believe the ordering rules have changed materially since 8.4; >> it's intended to be by object kind, and within that by name, except >> where dependencies force doing something else. > I thought that didn't matter, since FK and PK constraints were added (in the > required order) after all data was loaded). >> But what are you thinking constitutes the "dump order" in a parallel dump? > I don't understand your question. Perhaps I don't understand *your* question. What concrete problem are you having? regards, tom lane
Re: pg_dump order of operation
On 08/26/2018 01:42 PM, Tom Lane wrote: Ron writes: On 08/26/2018 10:24 AM, Tom Lane wrote: Ron writes: In what order does the 9.6 pg_dump dump tables? I don't believe the ordering rules have changed materially since 8.4; it's intended to be by object kind, and within that by name, except where dependencies force doing something else. I thought that didn't matter, since FK and PK constraints were added (in the required order) after all data was loaded). But what are you thinking constitutes the "dump order" in a parallel dump? I don't understand your question. Perhaps I don't understand *your* question. What concrete problem are you having? I want to track the progress of pg_dump so as to estimate completion time. -- Angular momentum makes the world go 'round.
Re: pg_dump order of operation
Ron writes: > On 08/26/2018 01:42 PM, Tom Lane wrote: >> Perhaps I don't understand *your* question. What concrete problem are you >> having? > I want to track the progress of pg_dump so as to estimate completion time. Well, if you don't use --jobs then you should get more or less the same behavior as in 8.4. If you do use that, then it's hardly surprising that things are more complicated. It looks like there's a hack in there to order tables by decreasing size during a parallel dump, on the theory that we'll get more parallelism that way. (Not sure I believe that, and the part of it that also orders index dumps that way is certainly brain-dead.) regards, tom lane
Re: pg_dump order of operation
On 08/26/2018 02:44 PM, Tom Lane wrote: Ron writes: On 08/26/2018 01:42 PM, Tom Lane wrote: Perhaps I don't understand *your* question. What concrete problem are you having? I want to track the progress of pg_dump so as to estimate completion time. Well, if you don't use --jobs then you should get more or less the same behavior as in 8.4. If you do use that, then it's hardly surprising that things are more complicated. I'm not demanding that it be simple, but just asking what the pattern is. -- Angular momentum makes the world go 'round.
Re: pg_sample
sudo yum install perl-CPAN Also do a "yum search perl-" and you should find most, if not all what you need natively packaged for your particular Linux distro. You're better off just sticking with the pre-packaged perl modules unless you specifically need something special. You'll find more modules if you also enable the EPEL yum repo by setting enabled=1 in the first section of /etc/yum.repos.d/epel.conf and rerunning that yum search command. On Sun, Aug 26, 2018, 2:20 PM Naveen Dabas wrote: > Sir i'm getting error in both commands > > [root@ip-88-8-8-17 ~]# perl -MCPAN -e 'install Bundle::DBI' > Can't locate CPAN.pm in @INC (@INC contains: /usr/local/lib64/perl5 > /usr/local/share/perl5 /usr/lib64/perl5/vendor_perl > /usr/share/perl5/vendor_perl /usr/lib64/perl5 /usr/share/perl5 .). > BEGIN failed--compilation aborted. > [root@ip-88-8-8-17 ~]# perl -MCPAN -e 'install DBD::Pg' > Can't locate CPAN.pm in @INC (@INC contains: /usr/local/lib64/perl5 > /usr/local/share/perl5 /usr/lib64/perl5/vendor_perl > /usr/share/perl5/vendor_perl /usr/lib64/perl5 /usr/share/perl5 .). > BEGIN failed--compilation aborted. > > > > operating system =CentOS Linux release 7.5.1804 (Core) > > thanks > > > On Fri, Aug 24, 2018 at 6:16 PM, Abhinav Mehta > wrote: > >> Solution, execute this on your linux terminal - >> >> $ perl -MCPAN -e 'install Bundle::DBI' >> $ perl -MCPAN -e 'install DBD::Pg' >> >> > On 24-Aug-2018, at 6:13 PM, Ravi Krishna wrote: >> > >> >> >> >> sir have taken pg_sample >> >> Now i want to run pg_sample with credential but i'm getting this error >> >> >> >> Can't locate DBI.pm in @INC (@INC contains: /usr/local/lib64/perl5 >> /usr/local/share/perl5 /usr/lib64/perl5/vendor_perl >> /usr/share/perl5/vendor_perl /usr/lib64/perl5 /usr/share/perl5 .) at >> ./pg_sample line 192. >> >> BEGIN failed--compilation aborted at ./pg_sample line 192. >> > >> > As is clear from the message, you need to install Perl DBI/DBD first. >> > >> > >> >> > > > -- > -- > With Regards > Naveen Dabas > Ph. 9017298370 > > > *Important Disclaimer:* Information contained in this email is for the > recipient primarily addressed to. If you are not the primary recipient or > are not supposed to receive this email, you are advised to kindly delete > the email or the thread and notify of the error. The logo is a registered > and copyrighted property of *ACTAS TECHNOLOGIES PRIVATE LIMITED*. Do not > use it without authorization.
Re: pg_sample
Server encoding is UTF8
Client encoding is UTF8
Creating sample schema sampledb1
DBD::Pg::db do failed: ERROR: cannot execute CREATE SCHEMA in a read-only
transaction at ./pg_sample line 296.
main::__ANON__('DBD::Pg::db do failed: ERROR: cannot execute CREATE SCHEMA
i...', 'DBI::db=HASH(0xab2388)', undef) called at ./pg_sample line 478
Done.
can you help me in this
thanks
On Mon, Aug 27, 2018 at 11:16 AM, Naveen Dabas wrote:
> Thanks for the help.
> But now i'm getting some different error as i rum this command
> ./pg_sample -a -f file.sql --limit="user = 100" --schema=dbname -h
> hostname -U user -W password
>
> I am not getting output and one file opens , in that file there id guide
> for pg_sample
>
> /tmp/9REOT5C
>
> NAME
> pg_sample - extract a small, sample dataset from a larger PostgreSQL
> database while maintaining referential integrity.
> =head1 SYNOPSIS
> pg_sample [ option... ] [ dbname ]
> =head1 DESCRIPTION
> pg_sample is a utility for exporting a small, sample dataset from a
> larger PostgreSQL database. The output and command-line options closely
> resemble the pg_dump backup utility (although only the plain-text format
> is supported).
> The sample database produced includes all tables from the original,
> maintains referential integrity, and supports circular dependencies.
> To build an actual instance of the sample database, the output of this
> script
> can be piped to the psql utility. For example, assuming we have an existing
> PostgreSQL database named "mydb", a sample database could be constructed
> with:
> createdb sampledb
> pg_sample mydb | psql sampledb
> and so on ...
>
> On Mon, Aug 27, 2018 at 1:27 AM, Paul Carlucci
> wrote:
>
>> sudo yum install perl-CPAN
>>
>> Also do a "yum search perl-" and you should find most, if not all what
>> you need natively packaged for your particular Linux distro. You're better
>> off just sticking with the pre-packaged perl modules unless you
>> specifically need something special.
>>
>> You'll find more modules if you also enable the EPEL yum repo by setting
>> enabled=1 in the first section of /etc/yum.repos.d/epel.conf and rerunning
>> that yum search command.
>>
>> On Sun, Aug 26, 2018, 2:20 PM Naveen Dabas wrote:
>>
>>> Sir i'm getting error in both commands
>>>
>>> [root@ip-88-8-8-17 ~]# perl -MCPAN -e 'install Bundle::DBI'
>>> Can't locate CPAN.pm in @INC (@INC contains: /usr/local/lib64/perl5
>>> /usr/local/share/perl5 /usr/lib64/perl5/vendor_perl
>>> /usr/share/perl5/vendor_perl /usr/lib64/perl5 /usr/share/perl5 .).
>>> BEGIN failed--compilation aborted.
>>> [root@ip-88-8-8-17 ~]# perl -MCPAN -e 'install DBD::Pg'
>>> Can't locate CPAN.pm in @INC (@INC contains: /usr/local/lib64/perl5
>>> /usr/local/share/perl5 /usr/lib64/perl5/vendor_perl
>>> /usr/share/perl5/vendor_perl /usr/lib64/perl5 /usr/share/perl5 .).
>>> BEGIN failed--compilation aborted.
>>>
>>>
>>>
>>> operating system =CentOS Linux release 7.5.1804 (Core)
>>>
>>> thanks
>>>
>>>
>>> On Fri, Aug 24, 2018 at 6:16 PM, Abhinav Mehta
>>> wrote:
>>>
Solution, execute this on your linux terminal -
$ perl -MCPAN -e 'install Bundle::DBI'
$ perl -MCPAN -e 'install DBD::Pg'
> On 24-Aug-2018, at 6:13 PM, Ravi Krishna
wrote:
>
>>
>> sir have taken pg_sample
>> Now i want to run pg_sample with credential but i'm getting this
error
>>
>> Can't locate DBI.pm in @INC (@INC contains: /usr/local/lib64/perl5
/usr/local/share/perl5 /usr/lib64/perl5/vendor_perl
/usr/share/perl5/vendor_perl /usr/lib64/perl5 /usr/share/perl5 .) at
./pg_sample line 192.
>> BEGIN failed--compilation aborted at ./pg_sample line 192.
>
> As is clear from the message, you need to install Perl DBI/DBD first.
>
>
>>>
>>>
>>> --
>>> --
>>> With Regards
>>> Naveen Dabas
>>> Ph. 9017298370
>>>
>>>
>>> *Important Disclaimer:* Information contained in this email is for the
>>> recipient primarily addressed to. If you are not the primary recipient or
>>> are not supposed to receive this email, you are advised to kindly delete
>>> the email or the thread and notify of the error. The logo is a registered
>>> and copyrighted property of *ACTAS TECHNOLOGIES PRIVATE LIMITED*. Do
>>> not use it without authorization.
>>
>>
>
>
> --
> --
> With Regards
> Naveen Dabas
> Ph. 9017298370
>
>
--
--
With Regards
Naveen Dabas
Ph. 9017298370
--
*Important Disclaimer:* Information contained in this email is for the
recipient primarily addressed to. If you are not the primary recipient or
are not supposed to receive this email, you are advised to kindly delete
the email or the thread and notify of the error. The logo is a registered
and copyrighted property of *ACTAS TECHNOLOGIES PRIVATE LIMITED*. Do not
use it without authorization.
