Re: Converting Access .mdb to postgres

[Rich Shepard]

On Wed, 14 Aug 2019, Rich Shepard wrote:


Off the mail list.


Sorry all. I thought it went to only Adrian.

Rich

Re: Converting Access .mdb to postgres

[Rich Shepard]

On Wed, 14 Aug 2019, Rich Shepard wrote:


Thanks, Adrian. This looks like it has all the information I need.


Adrian,

Off the mail list.

I'm reading that metadata document and it seems to me that it's not well
constructed. Perhaps this is an Access thing[1]; perhaps the DBA's have
limited knowledge. For example there are a lot of entity1_X_entity2 tables
which I think don't need to exist as one can get the same information from a
query. And, there are a bunch of lookup tables I think could be better
handled the postgres way.

Would you help me with this migration off the mail list?

Regards,

Rich

[1] I tried searching the database tables on their web site but could not
find the information I need. As far as I know Access is a flat-file
database, not relational.

Re: Converting Access .mdb to postgres

[Rich Shepard]

On Wed, 14 Aug 2019, Adrian Klaver wrote:


Here:
https://www.streamnet.org/data/downloadable-data/
Link: Data Exchange Standard (DES).


Adrian,

Then I missed it when I was in that subdirectory.

Thanks,

Rich

Re: Converting Access .mdb to postgres

[Adrian Klaver]

On 8/14/19 11:11 AM, Rich Shepard wrote:

On Wed, 14 Aug 2019, Adrian Klaver wrote:


Have you looked at:
https://www.streamnet.org/wp-content/uploads/2018/06/StreamNetExchangeStandard2018-1.doc 



Thanks, Adrian. This looks like it has all the information I need.

Under which menu did you find this? I had looked in the Data and Resources
menus and searched for 'data exchange' without finding it.


Here:

https://www.streamnet.org/data/downloadable-data/
Link: Data Exchange Standard (DES).



Regards,

Rich






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

Re: Converting Access .mdb to postgres

[Rich Shepard]

On Wed, 14 Aug 2019, Michael Nolan wrote:


It seemed like for every rule I tried there were a handful of exceptions.
We wound up just rewriting the app and not trying to export the data from
the previous one.


Mike,

This is not surprising. My interest is in the data, not the application.
Much of my consulting practice involves fish distributions, abundances, and
habits. The streamnet database has always been a comprehensive resource for
this information within the Columbia River basin.

Regards,

Rich

Re: Converting Access .mdb to postgres

[Rich Shepard]

On Wed, 14 Aug 2019, Adrian Klaver wrote:


Have you looked at:
https://www.streamnet.org/wp-content/uploads/2018/06/StreamNetExchangeStandard2018-1.doc


Thanks, Adrian. This looks like it has all the information I need.

Under which menu did you find this? I had looked in the Data and Resources
menus and searched for 'data exchange' without finding it.

Regards,

Rich

Re: Converting Access .mdb to postgres

[Michael Nolan]

A few years ago I tried to take an app someone had written for us in
Access  years ago and convert it to Postgres.

It seemed like for every rule I tried there were a handful of exceptions.

We wound up just rewriting the app and not trying to export the data from
the previous one.

I hope your project is more successful at extracting the data than ours was.
--
Mike Nolan

Re: Converting Access .mdb to postgres

[Rich Shepard]

On Wed, 14 Aug 2019, Rich Shepard wrote:


I could post the .mdb on a cloud web site for download; it's 565M
uncompressed; the xz-compressed tarball is a mere 42M.



Will post the URL for the tarball Real Soon Now.


Here it is for anyone interested: .

Rich

Re: Converting Access .mdb to postgres

[Rich Shepard]

On Wed, 14 Aug 2019, Adrian Klaver wrote:


Have you looked at:
https://www.streamnet.org/wp-content/uploads/2018/06/StreamNetExchangeStandard2018-1.doc


Adrian,

Not yet, but I will. Didn't see it when I went to the data pages.

Thanks,

Rich

Re: Converting Access .mdb to postgres

[Rich Shepard]

On Wed, 14 Aug 2019, Roger De Four wrote:


The easy way - just send it to me.

The more challenging way requires using several tools like excel or Open
Office.


Roger,

I could post the .mdb on a cloud web site for download; it's 565M
uncompressed; the xz-compressed tarball is a mere 42M.

When I ran mdb-export I seleted postgres output. The tables are all insert
into statements of the data. I don't know that LibreOffice would handle
files of 112M (the size of one table as downloaded from their web site), but
emacs had no issues with it. 'Excel' or any other proprietary application is
not an option for me. I defenestrated early in 1997 and run only linux.

Will post the URL for the tarball Real Soon Now.

Thanks,

Rich

Re: Converting Access .mdb to postgres

[Adrian Klaver]

On 8/14/19 10:45 AM, Rich Shepard wrote:

On Wed, 14 Aug 2019, Adrian Klaver wrote:


So you have the tables in Postgres, correct?


Adrian,

Not yet. I have the schema extracted using mdb-schema.


I did something similar with the USDA Nutrient database(with notion of
making it a test dataset): https://ndb.nal.usda.gov/ndb/doc/index


This is from streamnet.org. They're part of the Pacific States Marine
Fisheries Commission and it's a database of all fish-related information
within the Columbia River basin.


Have you looked at:

https://www.streamnet.org/wp-content/uploads/2018/06/StreamNetExchangeStandard2018-1.doc



Access allows you to define 'virtual' relationships that are not really
enforced as I found out when looking at the data. Good luck on getting
information, my experience is agencies(I have also dealt with USFWS) do
not respond to requests about their data. If you do get the information I
guessing you will have to create your own method of dealing with it. 
Don't

count on the data being consistent.


At the federal level cooperation is spotty. Several years ago I downloaded
the International Taxonomic Identification System (ITIS) database (the
internationally accepted list of names -- scientific and common -- for all
animals (perhaps plants, too) in mysql format (I think that was the format)
and my contact there asked for the postgres database when done. I sent him
the dump_all output and he was happy. State level is more open.

Regards,

Rich






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

Re: Converting Access .mdb to postgres

[Rich Shepard]

On Wed, 14 Aug 2019, Adrian Klaver wrote:


So you have the tables in Postgres, correct?


Adrian,

Not yet. I have the schema extracted using mdb-schema.


I did something similar with the USDA Nutrient database(with notion of
making it a test dataset): https://ndb.nal.usda.gov/ndb/doc/index


This is from streamnet.org. They're part of the Pacific States Marine
Fisheries Commission and it's a database of all fish-related information
within the Columbia River basin.


Access allows you to define 'virtual' relationships that are not really
enforced as I found out when looking at the data. Good luck on getting
information, my experience is agencies(I have also dealt with USFWS) do
not respond to requests about their data. If you do get the information I
guessing you will have to create your own method of dealing with it. Don't
count on the data being consistent.


At the federal level cooperation is spotty. Several years ago I downloaded
the International Taxonomic Identification System (ITIS) database (the
internationally accepted list of names -- scientific and common -- for all
animals (perhaps plants, too) in mysql format (I think that was the format)
and my contact there asked for the postgres database when done. I sent him
the dump_all output and he was happy. State level is more open.

Regards,

Rich

Re: Converting Access .mdb to postgres

[Adrian Klaver]

On 8/14/19 10:09 AM, Rich Shepard wrote:

I have the need to convert a flat-file Access database to postgres. I've
exported the .mdb tables using mdbtools. There's an accompanying metadata
PDF with column names and data types for each of the 84 tables, but no
description of the tables or column headings. I've asked the agency to
provide that information ... if they have it. No table has primary or
referential keys and, of course, there's no E-R diagram of the schema.


So you have the tables in Postgres, correct?



If anyone's done this I'd appreciate learning from your experiences. And I
assume there are no tools to automate all or part of the process so it must
be done manually.


I did something similar with the USDA Nutrient database(with notion of 
making it a test dataset):

https://ndb.nal.usda.gov/ndb/doc/index

Their PDF:
https://www.ars.usda.gov/ARSUserFiles/80400525/Data/SR-Legacy/SR-Legacy_Doc.pdf

contained table and field descriptions as well as relationships. 
Unfortunately the relationship information was not really accurate. 
Access allows you to define 'virtual' relationships that are not really 
enforced as I found out when looking at the data. Good luck on getting 
information, my experience is agencies(I have also dealt with USFWS) do 
not respond to requests about their data. If you do get the information 
I guessing you will have to create your own method of dealing with it. 
Don't count on the data being consistent.





Among the tables are many lookup tables. I don't know whether to leave them
as tables or apply a different structure to them.

Advice, suggestions, and recommendations are all welcome.

TIA,

Rich





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

Converting Access .mdb to postgres

[Rich Shepard]

I have the need to convert a flat-file Access database to postgres. I've
exported the .mdb tables using mdbtools. There's an accompanying metadata
PDF with column names and data types for each of the 84 tables, but no
description of the tables or column headings. I've asked the agency to
provide that information ... if they have it. No table has primary or
referential keys and, of course, there's no E-R diagram of the schema.

If anyone's done this I'd appreciate learning from your experiences. And I
assume there are no tools to automate all or part of the process so it must
be done manually.

Among the tables are many lookup tables. I don't know whether to leave them
as tables or apply a different structure to them.

Advice, suggestions, and recommendations are all welcome.

TIA,

Rich

How to conditionally change the 2nd part of a full join

[Moreno Andreo]

I need to obtain a single record from 2 records in the same table 
grouping for a key, say

id    value    value2
1 2            5
1    2         7

the result would be
1    2    5    7
and that works fine with a full join:
SELECT * FROM
(SELECT idp, data, i1, i2, m1, m2 from tblwk WHERE recordkey = 4) s1
FULL JOIN
(SELECT idp, data, i1, m1, m2 from tblwk WHERE recordkey = 10) s2
USING (data, idp)

Now, to get another dataset, I need the second subselect to change based 
on a value acquired from the first one. I tried with a case


SELECT * FROM (
    SELECT idp, data, idd, rif1, rif2, t1, t2, t3, t5, t7, t9, t10, i1, 
i2, i3, dg from tblwk WHERE recordkey = 1) s1

    FULL JOIN
       case
       when i1=1 then (SELECT idp, data, desc, rif1, rif3, t1, t2, 
t5 from tblwk WHERE recordkey = 2) s2
       when i1=2 then (SELECT idp, data, desc, rif1, t4, t5, i2 
from tblwk WHERE recordkey = 3 order by i2) s2
       when i1=3 then (SELECT idp, data, desc, rif1, t2, t5, t6, i2 
from tblwk WHERE recordkey = 4 order by i2) s2
       when i1=4 then (SELECT idp, data, desc, i2 from tblwk WHERE 
recordkey = 9) s2


       end

       USING (data, idp)
but it doesn't like "case" after a FULL JOIN.
I read a bit of docs and discovered LATERAL, but AFAIK it's useless here.
Is it necessary to write a function (which would be my last resort, not 
just because I'm not so good in writing functions) or there is some SQL 
syntax that can come in help?

Thanks
Moreno.-

Re: ORA-24345: A Truncation or null fetch error occurred -ora2pg

[Adrian Klaver]

On 8/14/19 2:39 AM, Daulat Ram wrote:

Hi Adrian ,

We have the below output. What we need to change.


I am not an ora2pg user so I don't know what to suggest for below. I 
would say the best thing to do would be to file an issue here:


https://github.com/darold/ora2pg/issues

Along with the original error message include the below and the 
settings, if any, for NLS_*, CLIENT_ENCODING from your ora2pg.conf file.




bash-4.2$ ora2pg -c ora2pg.bidder.conf -t SHOW_ENCODING

Current encoding settings that will be used by Ora2Pg:
 Oracle NLS_LANG AMERICAN_AMERICA.AL32UTF8
 Oracle NLS_NCHAR AL32UTF8
 Oracle NLS_TIMESTAMP_FORMAT -MM-DD HH24:MI:SS.FF6
 Oracle NLS_DATE_FORMAT -MM-DD HH24:MI:SS
 PostgreSQL CLIENT_ENCODING UTF8
 Perl output encoding ''
Showing current Oracle encoding and possible PostgreSQL client encoding:
 Oracle NLS_LANG AMERICAN_AMERICA.WE8MSWIN1252
 Oracle NLS_NCHAR WE8MSWIN1252
 Oracle NLS_TIMESTAMP_FORMAT -MM-DD HH24:MI:SS.FF6
 Oracle NLS_DATE_FORMAT -MM-DD HH24:MI:SS
 PostgreSQL CLIENT_ENCODING WIN1252
bash-4.2$

thanks



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

Re: GIST/GIN index not used with Row Level Security

[Derek Hans]

>
>
> > I've updated word_similarity_op(text,text) to be leakproof, and
> > pg_proc agrees it is. I'm assuming word_similarity_op() is equivalent to
> > <%, though I haven't found explicit confirmation. However, using
> > word_similarity() instead of <% on a 100k row table, without any RLS
> > involved, doesn't make use of the index, while using <% does. Obviously,
> > adding the RLS doesn't make that any better. Any idea what might be the
> > cause?
>
> Just to be clear, you should be looking at pg_operator (oprcode) to
> determine the function that is under the operator that you wish to
> change to being leakproof.
>
>
Thanks for that pointer.


> Note that the selectivity functions are associated with the operator,
> not the function itself.
>

That was the missing piece, thanks. How come operators get optimized but
functions don't?

Quick summary:
The text similarity/full text search/like operators are not marked as
leakproof, which stops them from having access to table statistics. When
combined with row level security, operators that aren't leakproof can't get
pushed down and therefore happen after the RLS check, preventing use of
GIN/GIST indexes. A workaround is marking the underlying function as
leakproof but that is only reasonable because our particular setup makes it
acceptable if information leaks via database error messages.

To resolve:
- Lookup function associated with operator being used via the pg_operator
table
- Check if that function is leakproof based on info in pg_proc table
- ALTER FUNCTION func LEAKPROOF
- Use original operator in code - the underlying function doesn't get
optimized and bypasses the index

While those steps work on my local machine, unfortunately we're deployed on
AWS Aurora which doesn't allow marking functions as leakproof. Functions
are owned by the rdsadmin user and controlled by AWS. In practice, that
appears to mean that fuzzy search/full text search with reasonable
performance isn't compatible with RLS on Amazon Aurora. We may end up
setting up Elasticsearch to support text search. In any case, we need to
separate search from checking who is allowed to see the results.

Thanks for the help from everyone!

RE: ORA-24345: A Truncation or null fetch error occurred -ora2pg

[Daulat Ram]

Hi Adrian ,

We have the below output. What we need to change. 

bash-4.2$ ora2pg -c ora2pg.bidder.conf -t SHOW_ENCODING

Current encoding settings that will be used by Ora2Pg:
Oracle NLS_LANG AMERICAN_AMERICA.AL32UTF8
Oracle NLS_NCHAR AL32UTF8
Oracle NLS_TIMESTAMP_FORMAT -MM-DD HH24:MI:SS.FF6
Oracle NLS_DATE_FORMAT -MM-DD HH24:MI:SS
PostgreSQL CLIENT_ENCODING UTF8
Perl output encoding ''
Showing current Oracle encoding and possible PostgreSQL client encoding:
Oracle NLS_LANG AMERICAN_AMERICA.WE8MSWIN1252
Oracle NLS_NCHAR WE8MSWIN1252
Oracle NLS_TIMESTAMP_FORMAT -MM-DD HH24:MI:SS.FF6
Oracle NLS_DATE_FORMAT -MM-DD HH24:MI:SS
PostgreSQL CLIENT_ENCODING WIN1252
bash-4.2$

thanks

 
-Original Message-
From: Adrian Klaver  
Sent: Tuesday, August 13, 2019 11:27 PM
To: Daulat Ram ; Luca Ferrari 
Cc: pgsql-performa...@lists.postgresql.org; pgsql-general@lists.postgresql.org
Subject: Re: ORA-24345: A Truncation or null fetch error occurred -ora2pg

On 8/13/19 10:34 AM, Daulat Ram wrote:
> H,
> 
> We are using  below the ora2pg version and the data types for tables.
> 
> bash-4.2$ ora2pg -v
> Ora2Pg v20.0
> bash-4.2$
> 
> SQL> SELECT distinct data_type FROM dba_tab_columns WHERE owner='GBOP;
> 
> DATA_TYPE
> 
> TIMESTAMP(6)
> FLOAT
> CLOB
> NUMBER
> CHAR
> DATE
> VARCHAR2
> BLOB
> 
> SQL>
> 
> We are getting the same issue for tables which are having blob, clob and char 
> data types.

The ora2pg issue below seems to have more information on this:

https://github.com/darold/ora2pg/issues/342

> 
> Thanks,
> Daulat
> 
> -Original Message-
> From: Luca Ferrari 
> Sent: Tuesday, August 13, 2019 8:32 PM
> To: Daulat Ram 
> Cc: pgsql-performa...@lists.postgresql.org; pgsql-general@lists.postgresql.org
> Subject: Re: ORA-24345: A Truncation or null fetch error occurred -ora2pg
> 
> On Tue, Aug 13, 2019 at 10:23 AM Daulat Ram  
> wrote:
>> Initially did not have LongReadLen set, so I thought this was the cause. 
>> But, I have set LongReadLen, on the db handle, equal to 9000.
> 
> Apparently this is an oracle problem because it acceppted data longer than 
> its type, so my guess would be that in your table you have a
> char(n) column that could be enlarged before the migration.
> 
> Hope this helps.
> And please report the version of ora2pg when asking for help.
> 
> Luca
> 


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

Re: Changing work_mem

[rihad]

On 08/14/2019 11:42 AM, Laurenz Albe wrote:

rihad wrote:

Sorry, I just decreased work_mem back to 256MB, reloaded, and
instantly started seeing 82mb temp file creation, not 165mb as was
usual with work_mem=512MB.

So it indeed was applied immediately.
Really weird figures )

Increased work_mem to 768MB and start seeing temp file creation log
entries 331MB in size.

Bizzare ) It looks like the bigger it gets, the bigger temp files
are
created.

Why not decrease it to 64mb then...

Temporary files are created whenever the data is estimated to not
fit into "work_mem".  So it is unsurprising that you see bigger
temporary files being created if you increase "work_mem".

Big temporary files will also be created when "work_mem" is small,
but maybe they got lost in the noise of the smaller files.
You should have noticed that fewer files are created when you increase
"work_mem".

Another thing to notice is that the temporary files use another, more
compact format than the data in memory, so you need to increase
"work_mem" to more than X if you want to avoid temporary files
of size X.

Yours,
Laurenz Albe


Thanks. In the end I increased work_mem to 2GB but temporary files are 
still being created, albeit at a much smaller total size (around 
0.2-0.25TB/day compared to 1TB/day of total disk write activity as 
witnessed by SMART's "Host_Writes_32MiB" attribute. The size of each 
file is also limited fro a few tens of bytes to no more than 90KB, so 
given their very short lifetime hopefully some of them stay inside OS 
buffers and do not even land on the SSD.


It's good that the memory is allocated by Postgres on an as-needed basis 
and freed when it is no longer needed. Thankfully those heavy queries 
employing xml are run periodically from cron and aren't part of the 
normal website activity.

Re: Changing work_mem

[Laurenz Albe]

rihad wrote:
> > Sorry, I just decreased work_mem back to 256MB, reloaded, and 
> > instantly started seeing 82mb temp file creation, not 165mb as was 
> > usual with work_mem=512MB.
> > 
> > So it indeed was applied immediately.
> > Really weird figures )
> 
> Increased work_mem to 768MB and start seeing temp file creation log 
> entries 331MB in size.
> 
> Bizzare ) It looks like the bigger it gets, the bigger temp files
> are 
> created.
> 
> Why not decrease it to 64mb then...

Temporary files are created whenever the data is estimated to not
fit into "work_mem".  So it is unsurprising that you see bigger
temporary files being created if you increase "work_mem".

Big temporary files will also be created when "work_mem" is small,
but maybe they got lost in the noise of the smaller files.
You should have noticed that fewer files are created when you increase
"work_mem".

Another thing to notice is that the temporary files use another, more
compact format than the data in memory, so you need to increase
"work_mem" to more than X if you want to avoid temporary files
of size X.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com