DBD::Pg::db do failed: ERROR:

[lpossamai]

I'm using PostgreSQL 9.2.15-1PGDG.rhel6 on a Centos 6.7 64 Bits.

When executing the command: ./pg_sample dbname --data-only --file=test.sql --limit="ja_admins = 1000"

I got the following error:

Creating table "_pg_sample"."pg_catalog_pg_statistic" DBD::Pg::db do failed: ERROR:  column "stavalues1" has pseudo-type "anyarray" at ./pg_sample line 296
    main::__ANON__('DBD::Pg::db do failed: ERROR:  column "stavalues1" has pseudo...', 'DBI::db=HASH(0xfdec08)', undef) called at ./pg_sample line 538
Dropping sample schema _pg_sample
Done.

Using --trace option:

)= undef at pg_sample line 538
    -> HandleError on DBI::db=HASH(0x289b660) via CODE(0x2858218) (undef)
DBD::Pg::db do failed: ERROR:  column "stavalues1" has pseudo-type "anyarray" at ./pg_sample line 296
    main::__ANON__('DBD::Pg::db do failed: ERROR:  column "stavalues1" has pseudo...', 'DBI::db=HASH(0x289b738)', undef) called at ./pg_sample line 538
       ERROR: 7 'ERROR:  column "stavalues1" has pseudo-type "anyarray"' (err#0)
    <- DESTROY(DBI::st=HASH(0x289bb70))= undef at Carp.pm line 45
Dropping sample schema _pg_sample
    !! The ERROR '7' was CLEARED by call to do method
    <- do('DROP SCHEMA _pg_sample CASCADE')= '0E0' at pg_sample line 713
Done.
!   <- DESTROY(DBI::db=HASH(0x289b738))= undef at pg_sample line 45 during global destruction

What can I do to solve this issue?

[mla]

That's strange.... I see no reference to "spatial" or "srid" in any of the foreign keys. So how are we getting the duplicate row?

Just as a sanity check, could you run this query?

SELECT srid, count(*) AS num_rows FROM spatial_ref_sys GROUP BY srid HAVING count(*) > 1;

We're expecting that to return nothing, since srid is the primary key.

[lpossamai]

yep... 0 rows .. lol

[mla]

Could you "grep -i inherits" the pg_sample output file? That's the only other sort of thing I could see causing this, if in fact the spatial_ref_sys table isn't participating in any foreign keys.

[lpossamai]

Yeah.. got nothing.. only comments...

I think will do this way and see if I get any issue after the DB is imported...

Thanks @mla

[mla]

@lpossamai ok. If you are comfortable emailing me your full schema, I'll see if I can discover anything.

You could also try grepping the pg_sample output file for that partial PK value, 3819, just to prove it actually is being duplicated. Although that value is pretty short so you may get a lot of false positives.

[lpossamai]

awesome.. thank you so much @mla !

[mla]

Are u dropping the dest. db prior to retrying the import?

On Sunday, March 6, 2016, lpossamai notifications@github.com wrote:

awesome.. thank you so much @mla https://github.com/mla !

— Reply to this email directly or view it on GitHub https://github.com/mla/pg_sample/issues/5#issuecomment-193051590.

[lpossamai]

yep... @mla

[mla]

Aha! It's from postgis: http://postgis.refractions.net/documentation/manual-1.4/ch04.html#spatial_ref_sys

So, I'm not sure if pg_sample should even be sampling those tables? I haven't worked with postgis...

[mla]

k, so I think that's it. There's this line from the schema definition:

CREATE EXTENSION IF NOT EXISTS postgis WITH SCHEMA public;

That adds the postgis support, and as part of that, it creates and populates the spatial_ref_sys table. So when our sampled content from that table is loaded, you get this duplicate key error.

So now I need to investigate if there's a way to differentiate between tables created via extensions vs. normal tables.

[SerpentM]

Hi, I am new to PostgreSQL and was looking for a way to get limited sample data.

I am using the script with following command.

./pg_sample --limit="*=10"--file=sample-beta.sql --data-only --verbose --host=XX.XX.XXX.00 --port=0000 --username=ethnic -password=XXXXXXXXXXX database_name But I got the following error ...

Server encoding is UTF8 Client encoding is UTF8 Creating sample schema _pg_sample [limit] .* = 10--file=sample-beta.sql [limit] .* = 100 Creating table "_pg_sample"."public_accounts" DBD::Pg::db do failed: ERROR: syntax error at end of input LINE 9: ^ at ./pg_sample line 300. main::__ANON__("DBD::Pg::db do failed: ERROR: syntax error at end of input\x{a}L"..., DBI::db=HASH(0x557adc0e7cb8), undef) called at ./pg_sample line 634 Dropping sample schema _pg_sample Done. How can I resolve this issue? please help me ...

[mla]

Hey @SerpentM. Take a look at the spacing between the options there. It looks like maybe the limit is being read as "10--file=sample-beta.sql" and is causing issues there. Make sure there's a space before the --file option.

[SerpentM]

Thanks man you're a life saver🥲