case and accent insensitive

[MOISES ESPINOSA]

Hi,

I need to reproduced the following Oracle functionality in PostgreSQL:

CREATE INDEX index_ciudad ON world.ciudad
((NLSSORT(NOMBRE29,'nls_sort=''GENERIC_BASELETTER''')));

I don't know how i could reproduced case insensitive and accent insensitive.

Is there any collate?

Thanks.

Re: pg_stat_statements: password in command is not obfuscated

[David Rowley]

On 24 March 2018 at 10:30, legrand legrand  wrote:
> It seems that passwords used in commands are not removed when caught by
> pg_stat_statements
> (they are not "normalized" being utility statements)
>
> exemple:
> alter role tt with password '123';
>
> select query from public.pg_stat_statements
> where query like '%password%';
>
> query
> 
> alter role tt with password '123';
>
> Do you think its a bug ?

If it is, then it's not a bug in pg_stat_statements. log_statement =
'ddl' would have kept a record of the same thing.

Perhaps the best fix would be a documentation improvement to mention
the fact and that it's best not to use plain text passwords in
CREATE/ALTER ROLE. Passwords can be md5 encrypted.

-- 
 David Rowley   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

Re: FDW Foreign Table Access: strange LOG message

[Tom Lane]

Albrecht =?iso-8859-1?b?RHJl3w==?=  writes:
> A different, confusing point (which is closer to a “bug” IMHO) is that 
> connections to localhost are actually encrypted by default.  This is 
> basically useless and just a waste of cpu cycles – if a malicious user may 
> somehow tap (tcpdump) lo, there is a different problem which can not be 
> mitigated by encryption…

I agree that it's not very useful to do that, but it'd be tough for us to
make it not happen by default --- that requires knowing an awful lot about
the local network topology.  Not sure that we'd want to assume that
"localhost" is safe, and we'd certainly not know what to do for
connections that use the host's name.

Note that in most scenarios, "local" connections travel over a Unix socket
not TCP, and in that case we don't encrypt.

regards, tom lane

pg_stat_statements: password in command is not obfuscated

[legrand legrand]

Hello,

It seems that passwords used in commands are not removed when caught by
pg_stat_statements
(they are not "normalized" being utility statements) 

exemple:
alter role tt with password '123';

select query from public.pg_stat_statements
where query like '%password%';

query

alter role tt with password '123';

Do you think its a bug ?

Regards
PAscal



--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

Re: FDW Foreign Table Access: strange LOG message

[Albrecht Dreß]

Hi Tom:

Am 22.03.18 23:55 schrieb(en) Tom Lane:

It finally occurred to me what is a likely explanation for why you're seeing 
these messages but I don't when I test postgres_fdw locally: probably, you are 
encrypting connections to the foreign server with SSL.


I can confirm that the confusing LOG messages go away when I disable SSL in the 
server definition, i.e. either by explicitly stating

CREATE SERVER ext_server FOREIGN DATA WRAPPER postgres_fdw OPTIONS (
dbname 'ext_db', host 'localhost', sslmode 'disable', updatable 
'false');

or by just saying

CREATE SERVER ext_server FOREIGN DATA WRAPPER postgres_fdw OPTIONS (
dbname 'ext_db', updatable 'false');

Linking different data bases on the same machine using the FDW may be a special 
use case, though…


Anyway, it's clearly not very nice that postgres_fdw makes no attempt to do a 
graceful shutdown of its remote connection.  I don't know that this rises to 
the level of a bug, exactly, but if somebody wanted to send in a patch it'd 
probably get accepted.


Knowing the background and the workarounds above, I agree with you that it's 
basically noise.  It would be nice if there was a note about it in the 
documentation, though – other users might stumble over it, too.

A different, confusing point (which is closer to a “bug” IMHO) is that 
connections to localhost are actually encrypted by default.  This is basically 
useless and just a waste of cpu cycles – if a malicious user may somehow tap 
(tcpdump) lo, there is a different problem which can not be mitigated by 
encryption…

Thank you so much for your help and insights!

Cheers,
Albrecht.

pgpv2YS1BSTAs.pgp
Description: PGP signature

Re: Postgresql 10.3 , query never completes if LIMIT clause is specified and paralle processing is on

[Alessandro Aste]

 PS , in the meanwhile I discovered a 2nd workaround(beside disabling
parallel processing) . I added offset  0 to the subquery , and, according
to the documentation, “OFFSET 0 is the same as omitting the OFFSET clause” -
 https://www.postgresql.org/docs/current/static/queries-limit.html

cmd3dev=# show max_parallel_workers_per_gather ;

*max_parallel_workers_per_gather*

*-*

*8*

(1 row)



cmd3dev=# \timing

Timing is on.

cmd3dev=#  SELECT * FROM (SELECT seg.circuit_id AS id,
vendor_gtt_pop.vendor_id, CASE WHEN vendor_gtt_pop.push_to_gaa = 1 THEN 'Y'
END as gaa, pop.gii_circuitid AS pop_name, cst.label AS pop_status,
seg.a_company_name AS pop_location, seg.vendor_id AS pop_provider_id,
seg.vendor_name AS pop_provider, cs.address1 AS pop_street, cs.city AS
pop_city, cs.postal_code AS pop_postal_code, cs.state AS pop_state,
csc.code AS pop_country, cs.country_id AS pop_country_id FROM ( SELECT
c.gii_circuitid, max(so.id) AS service_order_id FROM service_order so join
circuit c on c.product_id=so.product_id join master_service_order mso on
mso.id=so.master_service_order_id WHERE NOT (so.ordertype_id = 2 AND
so.status <> 999) AND NOT (so.ordertype_id = 3 AND so.status <> 999) AND
c.status >= 20 AND c.status not in (160,999) AND mso.client_id=11615 AND
c.service_description=28 AND c.status!=160 GROUP BY c.gii_circuitid ) pop
JOIN service_order so ON so.id = pop.service_order_id left JOIN client_site
cs on cs.id=so.a_site_id left JOIN country csc on csc.id=cs.country_id JOIN
circuit c ON so.product_id=c.product_id JOIN circuit_status cst ON
cst.id=c.status
JOIN ( SELECT c.id AS circuit_id, sg.id AS segment_id,
c.pop_support_vendor_id AS vendor_id, v.name AS vendor_name,
sg.a_company_name FROM segment sg JOIN circuit_layout cl ON cl.segment_id =
sg.id AND cl.ordinal = 1 JOIN circuit c ON c.id = cl.circuit_id JOIN vendor
v ON v.id = c.pop_support_vendor_id ) seg ON seg.circuit_id = c.id JOIN
vendor_gtt_pop on vendor_gtt_pop.gtt_pop_id = seg.circuit_id offset 0) foo
where vendor_id = 12346 AND (1=1) ORDER BY pop_name ASC,id desc LIMIT 10;

   id   | vendor_id | gaa |   pop_name| pop_status | pop_location |
pop_provider_id | pop_provider | pop_street  | pop_city | pop

_postal_code | pop_state | pop_country | pop_country_id

+---+-+---++--+-+--+-+--+

-+---+-+

684807 | 12346 | Y   | GTT/POP/LON1T | Active | LON1T
|   12288 | Telehouse UK | 14 Coriander Avenue | London   | E14

2AA |   | GB  |219

(1 row)



*Time: 2245.073 ms (00:02.245)*



On Fri, Mar 23, 2018 at 9:31 AM, Alessandro Aste 
wrote:

> Tomas, I'm attaching a 4MB file with the perf report. Let me know if it
> gets blocked, I'll shrink it to the first 1000 lines.
>
> Thank you,
>
> Alessandro.
>
> On Thu, Mar 22, 2018 at 11:42 PM, Tomas Vondra <
> tomas.von...@2ndquadrant.com> wrote:
>
>> On 03/22/2018 11:29 PM, Alessandro Aste wrote:
>> > Thanks Tomas. We're currently building postgres from source. In order to
>> > enable symbols, you want me to re-configure postres with  --enable-debug
>> > then run perf?
>> >
>>
>> Yes.
>>
>> regards
>>
>> --
>> Tomas Vondra  http://www.2ndQuadrant.com
>> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>>
>
>

Re: postgresql-10.3 on unbuntu-17.10 - how??

[Christoph Berg]

Re: Stuart McGraw 2018-03-23 
> So what I was thinking of was rebuilding the Pgdg Ubuntu source
> package (I'm assuming one is available somewhere).  I have had
> good results on Fedora backporting current versions of Postgresql
> from later fedora's to my invariably outdated version of Fedora
> by rebuilding the later version's Fedora postgresql source rpm.
> This produces an installable binary package that will satisfy
> the dependencies of all those other programs eliminating the
> need to rebuild them.  I was guessing I could do something
> similar in the Ubuntu world.  But, moot now fortunately :-)

Fwiw, all the packages on apt.postgresql.org are built from the same
source package for all distributions (with only the version number
changed to reflect the build target), so simply rebuilding the source
package for your OS would definitely work.

"apt-get source postgresql-10" will download it.

(Fine print: there's a few tweaks [1] applied to cater for older
distributions that lack newer features, e.g. the postgresql-10
(source) package in Debian wheezy and Ubuntu trusty disables the
systemd support, but that's rare exceptions, and really only applies
to the "old" distributions still supported.)

Re the original "where is artful" question, we try to support the
non-LTS releases, but it didn't work out this time. We'll add 18.10
if it becomes clear that the 18.04 LTS packages are incompatible with
it.

Christoph

[1] 
https://git.postgresql.org/gitweb/?p=pgapt.git;a=blob;f=jenkins/generate-pgdg-source;hb=HEAD#l181

Re: JDBC connectivity issue

[Dave Cramer]

Cool, thanks. I'd love to see this work

Dave Cramer

da...@postgresintl.com
www.postgresintl.com

On 23 March 2018 at 10:24, JCMS22  wrote:

> I forgot to say I am trying to investigate how to make it work as we need
> specific stuff from the DataSource object.
>
> It might work if you use setServerName rather than setUrl. setServerName
> will append itself the jdbc:postgresql:// so you only give it the server
> name (i.e "url1:port1,url2:port2") and then setPortNumber to 0. By reading
> the code, this might work.
>
>
>
> --
> Sent from: http://www.postgresql-archive.org/PostgreSQL-general-
> f1843780.html
>
>

RE: Foreign Key locking / deadlock issue.... v2

[HORDER Phil]

Rob, thanks for looking.



The "pause" is only to not-do-the-commit yet, so that the child process can 
then try and access the record - I've not left anything out.

This code is my own demo, not a cut from our production code.



Did you run this as the 'postgres' superuser?  That would bypass the RLS, and 
probably avoid the problem.



I checked by creating a new user, and ran my code in that:



Sql> create user test password 'password';



After running my test script, psql \dp shows:



Schema |   Name| Type  |Access privileges| Column 
privileges | Policies

+---+---+-+---+--

public | eln   | table | |  
 |

public | pl| table | |  
 | security_policy:+

|   |   | | 
  |   (u): true +

|   |   | | 
  |   (c): true



(plus some other stuff for postGIS)



Here’s my code again:



drop table if exists ELN;

drop table if exists PL;



Create table PL

(pl_id integer,

m_number text

);



alter table PL ENABLE row level security;

alter table PL FORCE row level security;



drop policy if exists security_policy on PL ;

CREATE POLICY security_policy on PL FOR ALL TO PUBLIC USING (true) WITH CHECK 
(true);



Alter table PL add constraint PL_PK primary key (pl_id);

Insert into PL values (1, null);

Insert into PL values (2, null);

Insert into PL values (3, null);



Create table ELN

(event_id integer,

pl_id integer

);





Alter table ELN add constraint ELN_PK primary key (event_id);

Alter table ELN add constraint ELN_PL_FK foreign key (pl_id) references PL 
(pl_id);



Insert into ELN values (301, null);

Insert into ELN values (302, null);

Insert into ELN values (303, null);



commit;



-- process 1:

start transaction;

update pl set m_number = '234' where pl_id = 2;

update pl set m_number = '345' where pl_id = 3;



-- wait here, and run process 2

commit;





-- process 2:

start transaction;

update eln set pl_id = 3 where event_id = 303;

update eln set pl_id = 2 where event_id = 302;

-- Gets blocked by process 1

commit;





Phil Horder

Database Mechanic



-Original Message-
From: rob stone [mailto:floripa...@gmail.com]
Sent: 23 March 2018 11:43
To: HORDER Phil; pgsql-general
Subject: Re: Foreign Key locking / deadlock issue v2



Hello Phil,



I've run your sample script on 9.6.5 and 10.3.

The only thing that I added was a commit; after the initial inserts just to 
ensure the rows were saved.

No errors were reported for either version.



The output of \dp after running was:-



Access privileges  Schema | Name | Type  | 
Access privileges | Column privileges

| Policies

+--+---+---+---+---

---

public | eln  | table |   |   |

 public | pl   | table |   |   |

security_policy:+

|  |   |   |   |   (u):

true





--> including the FOR ALL in the create policy statement as well as

WITH CHECK(true).



   Access privileges  Schema | Name | Type  | 
Access privileges | Column privileges

| Policies

+--+---+---+---+---

---

public | eln  | table |   |   |

 public | pl   | table |   |   |

security_policy:+

|  |   |   |   |   (u):

true +

|  |   |   |   |   (c):

true





The only mystery is what happens here:-







-- …. Pause while other processing happens …..

(commit;)



-- Child table processing – occurs often & quickly. Starts after parent update.



<\snip>





I'd like to know more about RLS and trying to de-bug your script.



On a production application you'd be testing for errors and raising exceptions 
so as to inform users that a problem occurred.



So, without knowing what occurs during "Pause while other processing happens" I 
can't help any further.



Cheers,

Rob

Re: JDBC connectivity issue

[JCMS22]

I forgot to say I am trying to investigate how to make it work as we need
specific stuff from the DataSource object. 

It might work if you use setServerName rather than setUrl. setServerName
will append itself the jdbc:postgresql:// so you only give it the server
name (i.e "url1:port1,url2:port2") and then setPortNumber to 0. By reading
the code, this might work.



--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

Re: JDBC connectivity issue

[JCMS22]

I am surprised this was not answered correct yet.

I am in the same bucket as you and here is the problem. A quick look at the
code and you find the answer.

The BaseDataSource class (from which PGSimpleDataSource inherits) does NOT
support failover addresses. The underlying driver used to parse the URL does
it correctly: it returns url1,url2,url3 for URL and port1,port2,port3 for
ports. However, the datasource interprets this as a single address so it
tries to convert the "port1,port2,port3" string to an Integerand it
crashes.



--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

RE: Use pgloader with FDW

[Patricia DECHANDOL]

We plan to build a Postgresql database connected to our mysql database to plug 
BI tools.
We will build materialized views in Postgresql. The datetime conversion logic 
will be seen in these views, I think.

Thanks for your advise.

-Message d'origine-
De : Alban Hertroys  
Envoyé : vendredi 23 mars 2018 14:58
À : Patricia DECHANDOL 
Cc : Adrian Klaver ; 
pgsql-general@lists.postgresql.org
Objet : Re: Use pgloader with FDW

I suppose MySQL has the concept of views. Perhaps you can create a view over 
the table that translates those 0-datetimes to NULL and have the FDW connect to 
the view instead?

On 23 March 2018 at 14:27, Patricia DECHANDOL  
wrote:
> Thanks a lot Adrian.
> We can't update the mysql columns values, so I will adopt the datetime -> 
> text solution.
> Thanks again
>
>
> -Message d'origine-
> De : Adrian Klaver  Envoyé : vendredi 23 
> mars 2018 14:25 À : Patricia DECHANDOL ; 
> pgsql-general@lists.postgresql.org
> Objet : Re: Use pgloader with FDW
>
> On 03/23/2018 06:07 AM, Patricia DECHANDOL wrote:
>> Hello Adrian,
>>
>> So, if I well understand, the only solution is to wrap the mysql datetime 
>> columns to "text" columns in my foreign tables.
>> And then use a cast function to convert from text to date in Postgre when I 
>> want to use these columns ?
>
> There is also the option of changing the values in the MySQL database to 
> either an actual datetime or NULL. Though there is the potential issue of 
> what that would do to code that is pulling from the MySQL database.
>
>>
>> No other way.
>> The pgloader can't be used by the FDW to manage this point ?
>
> It has been a while since I used pgloader, but from what I remember it is a 
> tool for doing the initial migration of data from MySQL/Sqlite/SQL Server to 
> Postgres. What you seem to be looking for is pgloader to sit between the 
> MySQL database and the Postgres one and do the transformation in real time. 
> AFAIK that is not possible.
>
>>
>>
>>
>> -Message d'origine-
>> De : Adrian Klaver  Envoyé : vendredi 23 
>> mars 2018 13:52 À : Patricia DECHANDOL ; 
>> pgsql-general@lists.postgresql.org
>> Objet : Re: Use pgloader with FDW
>>
>> On 03/23/2018 03:55 AM, Patricia DECHANDOL wrote:
>>> Hello,
>>>
>>> I'm new to Postgresql and try to use the FDW with mysql database.
>>>
>>> Everything is OK to create my FDW and foreign tables, but I meet a 
>>> problem when I try to do a select on a FOREIGN table containing 
>>> datetime column which contains the value "-00-00 00:00:00" in mysql.
>>
>> That is MySQL's version of NULL for datetimes. It is not a valid datetime 
>> though.
>>
>>>
>>> Select on the foreign table fails.
>>>
>>> The mysql datetime column has been automatically wrapped to 
>>> "timestamp without timezone" column in the foreign table by the 
>>> instruction "import foreign schema" that I used.
>>
>>>
>>> How can I deal with this ?
>>
>> https://github.com/EnterpriseDB/mysql_fdw/issues/38
>>
>>>
>>> I read about the pgloader with can manage this king of problem, but 
>>> can't figure out how to use it with FDW.
>>>
>>> Thanks for your help.
>>>
>>
>>
>> --
>> Adrian Klaver
>> adrian.kla...@aklaver.com
>>
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com



--
If you can't see the forest for the trees, Cut the trees and you'll see there 
is no forest.

Re: Use pgloader with FDW

[Alban Hertroys]

I suppose MySQL has the concept of views. Perhaps you can create a
view over the table that translates those 0-datetimes to NULL and have
the FDW connect to the view instead?

On 23 March 2018 at 14:27, Patricia DECHANDOL
 wrote:
> Thanks a lot Adrian.
> We can't update the mysql columns values, so I will adopt the datetime -> 
> text solution.
> Thanks again
>
>
> -Message d'origine-
> De : Adrian Klaver 
> Envoyé : vendredi 23 mars 2018 14:25
> À : Patricia DECHANDOL ; 
> pgsql-general@lists.postgresql.org
> Objet : Re: Use pgloader with FDW
>
> On 03/23/2018 06:07 AM, Patricia DECHANDOL wrote:
>> Hello Adrian,
>>
>> So, if I well understand, the only solution is to wrap the mysql datetime 
>> columns to "text" columns in my foreign tables.
>> And then use a cast function to convert from text to date in Postgre when I 
>> want to use these columns ?
>
> There is also the option of changing the values in the MySQL database to 
> either an actual datetime or NULL. Though there is the potential issue of 
> what that would do to code that is pulling from the MySQL database.
>
>>
>> No other way.
>> The pgloader can't be used by the FDW to manage this point ?
>
> It has been a while since I used pgloader, but from what I remember it is a 
> tool for doing the initial migration of data from MySQL/Sqlite/SQL Server to 
> Postgres. What you seem to be looking for is pgloader to sit between the 
> MySQL database and the Postgres one and do the transformation in real time. 
> AFAIK that is not possible.
>
>>
>>
>>
>> -Message d'origine-
>> De : Adrian Klaver  Envoyé : vendredi 23
>> mars 2018 13:52 À : Patricia DECHANDOL ;
>> pgsql-general@lists.postgresql.org
>> Objet : Re: Use pgloader with FDW
>>
>> On 03/23/2018 03:55 AM, Patricia DECHANDOL wrote:
>>> Hello,
>>>
>>> I'm new to Postgresql and try to use the FDW with mysql database.
>>>
>>> Everything is OK to create my FDW and foreign tables, but I meet a
>>> problem when I try to do a select on a FOREIGN table containing
>>> datetime column which contains the value "-00-00 00:00:00" in mysql.
>>
>> That is MySQL's version of NULL for datetimes. It is not a valid datetime 
>> though.
>>
>>>
>>> Select on the foreign table fails.
>>>
>>> The mysql datetime column has been automatically wrapped to
>>> "timestamp without timezone" column in the foreign table by the
>>> instruction "import foreign schema" that I used.
>>
>>>
>>> How can I deal with this ?
>>
>> https://github.com/EnterpriseDB/mysql_fdw/issues/38
>>
>>>
>>> I read about the pgloader with can manage this king of problem, but
>>> can't figure out how to use it with FDW.
>>>
>>> Thanks for your help.
>>>
>>
>>
>> --
>> Adrian Klaver
>> adrian.kla...@aklaver.com
>>
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com



-- 
If you can't see the forest for the trees,
Cut the trees and you'll see there is no forest.

RE: Use pgloader with FDW

[Patricia DECHANDOL]

Thanks a lot Adrian.
We can't update the mysql columns values, so I will adopt the datetime -> text 
solution.
Thanks again


-Message d'origine-
De : Adrian Klaver  
Envoyé : vendredi 23 mars 2018 14:25
À : Patricia DECHANDOL ; 
pgsql-general@lists.postgresql.org
Objet : Re: Use pgloader with FDW

On 03/23/2018 06:07 AM, Patricia DECHANDOL wrote:
> Hello Adrian,
> 
> So, if I well understand, the only solution is to wrap the mysql datetime 
> columns to "text" columns in my foreign tables.
> And then use a cast function to convert from text to date in Postgre when I 
> want to use these columns ?

There is also the option of changing the values in the MySQL database to either 
an actual datetime or NULL. Though there is the potential issue of what that 
would do to code that is pulling from the MySQL database.

> 
> No other way.
> The pgloader can't be used by the FDW to manage this point ?

It has been a while since I used pgloader, but from what I remember it is a 
tool for doing the initial migration of data from MySQL/Sqlite/SQL Server to 
Postgres. What you seem to be looking for is pgloader to sit between the MySQL 
database and the Postgres one and do the transformation in real time. AFAIK 
that is not possible.

> 
> 
> 
> -Message d'origine-
> De : Adrian Klaver  Envoyé : vendredi 23 
> mars 2018 13:52 À : Patricia DECHANDOL ; 
> pgsql-general@lists.postgresql.org
> Objet : Re: Use pgloader with FDW
> 
> On 03/23/2018 03:55 AM, Patricia DECHANDOL wrote:
>> Hello,
>>
>> I'm new to Postgresql and try to use the FDW with mysql database.
>>
>> Everything is OK to create my FDW and foreign tables, but I meet a 
>> problem when I try to do a select on a FOREIGN table containing 
>> datetime column which contains the value "-00-00 00:00:00" in mysql.
> 
> That is MySQL's version of NULL for datetimes. It is not a valid datetime 
> though.
> 
>>
>> Select on the foreign table fails.
>>
>> The mysql datetime column has been automatically wrapped to 
>> "timestamp without timezone" column in the foreign table by the 
>> instruction "import foreign schema" that I used.
> 
>>
>> How can I deal with this ?
> 
> https://github.com/EnterpriseDB/mysql_fdw/issues/38
> 
>>
>> I read about the pgloader with can manage this king of problem, but 
>> can't figure out how to use it with FDW.
>>
>> Thanks for your help.
>>
> 
> 
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
> 


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

Re: Use pgloader with FDW

[Adrian Klaver]

On 03/23/2018 06:07 AM, Patricia DECHANDOL wrote:

Hello Adrian,

So, if I well understand, the only solution is to wrap the mysql datetime columns to 
"text" columns in my foreign tables.
And then use a cast function to convert from text to date in Postgre when I 
want to use these columns ?


There is also the option of changing the values in the MySQL database to 
either an actual datetime or NULL. Though there is the potential issue 
of what that would do to code that is pulling from the MySQL database.




No other way.
The pgloader can't be used by the FDW to manage this point ?


It has been a while since I used pgloader, but from what I remember it 
is a tool for doing the initial migration of data from MySQL/Sqlite/SQL 
Server to Postgres. What you seem to be looking for is pgloader to sit 
between the MySQL database and the Postgres one and do the 
transformation in real time. AFAIK that is not possible.






-Message d'origine-
De : Adrian Klaver 
Envoyé : vendredi 23 mars 2018 13:52
À : Patricia DECHANDOL ; 
pgsql-general@lists.postgresql.org
Objet : Re: Use pgloader with FDW

On 03/23/2018 03:55 AM, Patricia DECHANDOL wrote:

Hello,

I'm new to Postgresql and try to use the FDW with mysql database.

Everything is OK to create my FDW and foreign tables, but I meet a
problem when I try to do a select on a FOREIGN table containing
datetime column which contains the value "-00-00 00:00:00" in mysql.


That is MySQL's version of NULL for datetimes. It is not a valid datetime 
though.



Select on the foreign table fails.

The mysql datetime column has been automatically wrapped to "timestamp
without timezone" column in the foreign table by the instruction
"import foreign schema" that I used.




How can I deal with this ?


https://github.com/EnterpriseDB/mysql_fdw/issues/38



I read about the pgloader with can manage this king of problem, but
can't figure out how to use it with FDW.

Thanks for your help.




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




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

RE: Use pgloader with FDW

[Patricia DECHANDOL]

Hello Adrian,

So, if I well understand, the only solution is to wrap the mysql datetime 
columns to "text" columns in my foreign tables.
And then use a cast function to convert from text to date in Postgre when I 
want to use these columns ?

No other way.
The pgloader can't be used by the FDW to manage this point ?



-Message d'origine-
De : Adrian Klaver  
Envoyé : vendredi 23 mars 2018 13:52
À : Patricia DECHANDOL ; 
pgsql-general@lists.postgresql.org
Objet : Re: Use pgloader with FDW

On 03/23/2018 03:55 AM, Patricia DECHANDOL wrote:
> Hello,
> 
> I'm new to Postgresql and try to use the FDW with mysql database.
> 
> Everything is OK to create my FDW and foreign tables, but I meet a 
> problem when I try to do a select on a FOREIGN table containing 
> datetime column which contains the value "-00-00 00:00:00" in mysql.

That is MySQL's version of NULL for datetimes. It is not a valid datetime 
though.

> 
> Select on the foreign table fails.
> 
> The mysql datetime column has been automatically wrapped to "timestamp 
> without timezone" column in the foreign table by the instruction 
> "import foreign schema" that I used.

> 
> How can I deal with this ?

https://github.com/EnterpriseDB/mysql_fdw/issues/38

> 
> I read about the pgloader with can manage this king of problem, but 
> can't figure out how to use it with FDW.
> 
> Thanks for your help.
> 


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

Re: Use pgloader with FDW

[Adrian Klaver]

On 03/23/2018 03:55 AM, Patricia DECHANDOL wrote:

Hello,

I’m new to Postgresql and try to use the FDW with mysql database.

Everything is OK to create my FDW and foreign tables, but I meet a 
problem when I try to do a select on a FOREIGN table containing datetime 
column which contains the value “-00-00 00:00:00” in mysql.


That is MySQL's version of NULL for datetimes. It is not a valid 
datetime though.




Select on the foreign table fails.

The mysql datetime column has been automatically wrapped to “timestamp 
without timezone” column in the foreign table by the instruction “import 
foreign schema” that I used.




How can I deal with this ?


https://github.com/EnterpriseDB/mysql_fdw/issues/38



I read about the pgloader with can manage this king of problem, but 
can’t figure out how to use it with FDW.


Thanks for your help.




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

Re: Foreign Key locking / deadlock issue.... v2

[rob stone]

Hello Phil,

I've run your sample script on 9.6.5 and 10.3.
The only thing that I added was a commit; after the initial inserts
just to ensure the rows were saved.
No errors were reported for either version.

The output of \dp after running was:-

Access privileges
 Schema | Name | Type  | Access privileges | Column privileges
| Policies 
+--+---+---+---+---
---
 public | eln  | table |   |   | 
 public | pl   | table |   |   |
security_policy:+
|  |   |   |   |   (u):
true


--> including the FOR ALL in the create policy statement as well as
WITH CHECK(true).

   Access privileges
 Schema | Name | Type  | Access privileges | Column privileges
| Policies 
+--+---+---+---+---
---
 public | eln  | table |   |   | 
 public | pl   | table |   |   |
security_policy:+
|  |   |   |   |   (u):
true +
|  |   |   |   |   (c):
true


The only mystery is what happens here:-



-- …. Pause while other processing happens …..
(commit;)
 
 
-- Child table processing – occurs often & quickly. Starts after parent
update.

<\snip>


I'd like to know more about RLS and trying to de-bug your script.

On a production application you'd be testing for errors and raising
exceptions so as to inform users that a problem occurred.

So, without knowing what occurs during "Pause while other processing
happens" I can't help any further.

Cheers,
Rob

Use pgloader with FDW

[Patricia DECHANDOL]

Hello,

I'm new to Postgresql and try to use the FDW with mysql database.
Everything is OK to create my FDW and foreign tables, but I meet a problem when 
I try to do a select on a FOREIGN table containing datetime column which 
contains the value "-00-00 00:00:00" in mysql.
Select on the foreign table fails.
The mysql datetime column has been automatically wrapped to "timestamp without 
timezone" column in the foreign table by the instruction "import foreign 
schema" that I used.

How can I deal with this ?
I read about the pgloader with can manage this king of problem, but can't 
figure out how to use it with FDW.

Thanks for your help.

Re: primary key and unique index

[Achilleas Mantzios]

On 23/03/2018 09:55, Thomas Poty wrote:

Hi all,

I am migrating fromMySQL to Postgresql 9.6.

In MySQL a  "show create table" gives me :
 ...
  PRIMARY KEY (`ID`,`CountryCode`,`LanguageCode`),
  UNIQUE KEY `unique_my table_4` (`ID`,`CountryCode`,`LanguageCode`),
...

So, In PostgreSQL, does it make sense to create a primary key AND a unique 
index based on the same columns?
Is PostgreSQL smart enough to use the unique index created for the primary key.



This is redundant. However, IMO it is always beneficial to have an bigint PK, 
set implicitly via a sequence.
So you could have smth like :

pkid BIGSERIAL PRIMARY KEY,
...
UNIQUE KEY unique_my table_4 ("ID","CountryCode","LanguageCode"),
...
This way, you get the artificial bigint PK (pkid), and the explicit natural 
unique key which enforces your business integrity.


I know PostgreSQL can be based on a unique index to create a primary key but I 
also know it is possible to create several indexes on the same columns with the 
same order.

Thanks

Thomas



--
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt

Re: primary key and unique index

[David Rowley]

On 23 March 2018 at 20:55, Thomas Poty  wrote:
> In MySQL a  "show create table" gives me :
>  ...
>   PRIMARY KEY (`ID`,`CountryCode`,`LanguageCode`),
>   UNIQUE KEY `unique_my table_4` (`ID`,`CountryCode`,`LanguageCode`),
> ...
>
> So, In PostgreSQL, does it make sense to create a primary key AND a unique
> index based on the same columns?
> Is PostgreSQL smart enough to use the unique index created for the primary
> key.

Doing this makes no sense in PostgreSQL.  I'm struggling to imagine
why it would in MySQL.


-- 
 David Rowley   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services