Re: Intersection or zero-column queries

[Ken Tanzer]

I noticed I get this behavior in 9.6, but in 9.2 an empty select results in
a syntax error.  Which just got me curious what caused the change, if it
was deliberate, and if one or the other is more proper behavior.

Cheers,
Ken

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

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

Re: [v10] CREATE TEMP FUNCTION/CREATE FUNCTION PG_TEMP.X

[Vincenzo Romano]

2017-12-22 0:50 GMT+01:00 Melvin Davidson :
>
>
>
> On Thu, Dec 21, 2017 at 12:07 PM, Vincenzo Romano 
>  wrote:
>>
>> 2017-12-21 17:56 GMT+01:00 Tom Lane :
>> > Vincenzo Romano  writes:
>> >> 2017-12-21 17:52 GMT+01:00 Tom Lane :
>> >>> You have to schema-qualify the temp function name when calling it, too.
>> >
>> >> So search_path is not used with functions?
>> >
>> > pg_temp is explicitly ignored when searching for functions/operators.
>> > Otherwise, installing a trojan horse is just too easy.
>> >
>> > regards, tom lane
>>
>> I'm not sure whether this decision actually makes PG more scure.
>> But, anyway, thanks for the insight: I've just found the
>> documentations for this.
>>
>> --
>> Vincenzo Romano - NotOrAnd.IT
>> Information Technologies
>> --
>> NON QVIETIS MARIBVS NAVTA PERITVS
>>
>
> Aside from the simple explanations you have received, I question your 
> justification for even having a temporary function.
> Functions are only entries in the system catalogs and as such, take up just a 
> tiny amount of physical space. In addition,
> if you ever need it again, you will have to expend time recreating it. Why 
> not just once and keep it?
>

Hi.
Thanks for your comment.

The reason for having temporary object, in my current design, is to
have something shadowing something else on a per session basis, thanks
to the search_path variable.
It's not simply a matter or storage room or access speed. Not at all to me.
If you use, for example:

SET search_path to pg_temp,"$user",public;

you can put general stuff in public, per-user data in "$user" and per
session data in pg_temp.
Then the "name resolution" will follow the above priority during lookup.
And, as I put more and more logics in the DB, having temporary
functions gives me a simple, clean and yet powerful design.

As soon as my applications connect, they run SELECT * FROM
f_application_init( 'MYAPPNAME' ).
That function (which is not temporary) will setup the DB-level, the
user-level and the session-level stuff.
Currently it eats about 500 msec to run and it's run only once per session.
So, the answer to your question is: "why not if it can be useful
"
-- 
Vincenzo Romano - NotOrAnd.IT
Information Technologies
--
NON QVIETIS MARIBVS NAVTA PERITVS

Re: Intersection or zero-column queries

[Tom Lane]

"David G. Johnston"  writes:
> On Thursday, December 21, 2017, Tom Lane  wrote:
>> So yeah, it's wrong ... but personally I'm not terribly excited
>> about fixing it.  Maybe somebody else wants to; but what's the
>> practical use?

> How about just erroring out?

Hm, yeah, inserting a FEATURE_NOT_SUPPORTED error might be an
appropriate amount of effort.

regards, tom lane

Re: Intersection or zero-column queries

[David G. Johnston]

On Thursday, December 21, 2017, Tom Lane  wrote:

> which would only be the right plan for UNION ALL.
>
> So yeah, it's wrong ... but personally I'm not terribly excited
> about fixing it.  Maybe somebody else wants to; but what's the
> practical use?
>

How about just erroring out?

David J.

Re: Intersection or zero-column queries

[Tom Lane]

Victor Yegorov  writes:
> However, if I'll do `EXCPET` or `INTERSECT` of such queries, I'll get 2
> rows:

> postgres=# select except select;
> --
> (2 rows)
> postgres=# select intersect all select;
> --
> (2 rows)

> Why is it so?

The UNION case seems wrong as well:

regression=# select union select;
--
(2 rows)

The reason is that the planner hasn't spent any time thinking about this
case:

/* Identify the grouping semantics */
groupList = generate_setop_grouplist(op, tlist);

/* punt if nothing to group on (can this happen?) */
if (groupList == NIL)
return path;

so what you actually get for any of these queries is a plan that
just appends the inputs and forgets to do any de-duplication:

regression=# explain select except select;
  QUERY PLAN   
---
 Append  (cost=0.00..0.04 rows=2 width=4)
   ->  Subquery Scan on "*SELECT* 1"  (cost=0.00..0.02 rows=1 width=4)
 ->  Result  (cost=0.00..0.01 rows=1 width=0)
   ->  Subquery Scan on "*SELECT* 2"  (cost=0.00..0.02 rows=1 width=4)
 ->  Result  (cost=0.00..0.01 rows=1 width=0)
(5 rows)

which would only be the right plan for UNION ALL.

So yeah, it's wrong ... but personally I'm not terribly excited
about fixing it.  Maybe somebody else wants to; but what's the
practical use?

regards, tom lane

Re: Foreign Data Wrapper

[Michael Paquier]

On Thu, Dec 21, 2017 at 12:24:25PM +, Virendra Shaktawat - Quipment India 
wrote:
> [Quipment Logo]
> I have stuck at foreign data wrapper. I am accessing the form MS Sql Server. 
> Foreign table has been created with data. unfortunately I am unable to 
> perform DML operation like insert, update and delete on foreign table. 
> Whenever I tried to perform DML operation on foreign table we are getting 
> error i.e. "ERROR: cannot insert into foreign table "test12"
> SQL state: 0A000"

You may want to see directly with the maintainers of the FDW you are using
for remote access. That's up to each FDW to use the set of APIs PostgreSQL
provides, and to support them when they come out.
-- 
Michael


signature.asc
Description: PGP signature

Re: Intersection or zero-column queries

[David G. Johnston]

On Thu, Dec 21, 2017 at 5:08 PM, Victor Yegorov  wrote:

>
> Also, intersection should not return more rows, than there're in the
> sub-relations.
>
>
Doh!, I think I got UNION into my mind somewhere in that...

David J.

Re: Intersection or zero-column queries

[Victor Yegorov]

2017-12-22 2:03 GMT+02:00 David G. Johnston :

> On Thu, Dec 21, 2017 at 4:53 PM, Victor Yegorov 
> wrote:
>
>> postgres=# select except select;
>> --
>> (2 rows)
>> postgres=# select intersect all select;
>> --
>> (2 rows)
>>
>> Why is it so?
>> Should this be reported as a bug?.. ;)
>>
>
> ​The intersection case seems correct - one row from each sub-relation is
> returned since ALL is specified and both results as the same.
>

Actually, result will not change with or without `ALL` for both, EXCEPT and
INTERSECT.

Also, intersection should not return more rows, than there're in the
sub-relations.


-- 
Victor Yegorov

Re: Intersection or zero-column queries

[David G. Johnston]

On Thu, Dec 21, 2017 at 4:53 PM, Victor Yegorov  wrote:

> postgres=# select except select;
> --
> (2 rows)
> postgres=# select intersect all select;
> --
> (2 rows)
>
> Why is it so?
> Should this be reported as a bug?.. ;)
>

​The intersection case seems correct - one row from each sub-relation is
returned since ALL is specified and both results as the same.

The except case looks like a bug because there should never be more rows
returned from the combined query than the upper sub-query returns alone.
Based upon the result of intersect it should in fact return zero rows -
unless this one of those null-like scenarios where it is both equal and not
equal at the same time...

David J.
​

Intersection or zero-column queries

[Victor Yegorov]

Greetings.

One can issue an empty `SELECT` statement and 1 row without columns will be
returned:

postgres=# select;
--
(1 row)

However, if I'll do `EXCPET` or `INTERSECT` of such queries, I'll get 2
rows:

postgres=# select except select;
--
(2 rows)
postgres=# select intersect all select;
--
(2 rows)

Why is it so?
Should this be reported as a bug?.. ;)


-- 
Victor Yegorov

Re: [v10] CREATE TEMP FUNCTION/CREATE FUNCTION PG_TEMP.X

[Melvin Davidson]

On Thu, Dec 21, 2017 at 12:07 PM, Vincenzo Romano <
vincenzo.rom...@notorand.it> wrote:

> 2017-12-21 17:56 GMT+01:00 Tom Lane :
> > Vincenzo Romano  writes:
> >> 2017-12-21 17:52 GMT+01:00 Tom Lane :
> >>> You have to schema-qualify the temp function name when calling it, too.
> >
> >> So search_path is not used with functions?
> >
> > pg_temp is explicitly ignored when searching for functions/operators.
> > Otherwise, installing a trojan horse is just too easy.
> >
> > regards, tom lane
>
> I'm not sure whether this decision actually makes PG more scure.
> But, anyway, thanks for the insight: I've just found the
> documentations for this.
>
> --
> Vincenzo Romano - NotOrAnd.IT
> Information Technologies
> --
> NON QVIETIS MARIBVS NAVTA PERITVS
>
>
Aside from the simple explanations you have received, I question your
justification for even having a temporary function.
Functions are only entries in the system catalogs and as such, take up just
a tiny amount of physical space. In addition,
if you ever need it again, you will have to expend time recreating it. Why
not just once and keep it?

-- 
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

Re: Foreign Data Wrapper

[Nicolas Paris]

Le 21 déc. 2017 à 13:24, Virendra Shaktawat - Quipment India écrivait :
> Quipment Logo
> 
> Hello ,
> 
>  
> 
> I have stuck at foreign data wrapper. I am accessing the form MS Sql Server.
> Foreign table has been created with data. unfortunately I am unable to perform
> DML operation like insert, update and delete on foreign table. Whenever I 
> tried
> to perform DML operation on foreign table we are getting error i.e. “ERROR:
> cannot insert into foreign table "test12"
> 
> SQL state: 0A000”
> 
>  
> 
> Kindly give me response soon because I am stuck at the middle of the project.
> 
>

Hi,

I would give a try to a specific MSSQL FDW such:
https://www.openscg.com/bigsql/docs/tds_fdw/

and the github:
https://github.com/tds-fdw/tds_fdw

While insert is not yet available there apparently some work have
already been done and could be improved
https://github.com/tds-fdw/tds_fdw/issues/9

Re: Foreign Data Wrapper

[John R Pierce]

On 12/21/2017 5:14 AM, Virendra Shaktawat - Quipment India wrote:


odbc_fdw



please don't top post, and please don't use graphics and HTML in your 
email, this is a text based mailling list adhering to bottom/interleaved 
posting standards.


While I'm not directly familiar with odbc_fdw, I suspect its a 'read 
only' FDW


is that this odbc_fdw?  https://github.com/ZhengYang/odbc_fdw
that was developed for pg 9.1 when ALL fdw's were read only as it was 
brand new technology. since this defines the foreign table via a query, 
well, there's no WAY it could be used to do an update.


this updated fork, https://github.com/CartoDB/odbc_fdw  runs on newer 
postgres, but also is based on a select statement, you can't UPDATE a 
resultset...





--
john r pierce, recycling bits in santa cruz

Re: Deadlock with one table - PostgreSQL is doing it right

[Rakesh Kumar]

> The tool Im using is "sqlplus". By default you are always in a transaction 
> and 
> auto-commit only occur on exit. 

Its been a while since I worked with oracle. I remember I use to add the 
following two lines at the top:
SET AUTOCOMMIT OFF
whenever SQLERROR EXIT ROLLBACK

IIRC, adding the above ensures that at the first error, oracle will rollback 
all changes , unless you add a DDL in the transaction which auto commits all 
changes.

Re: Deadlock with one table - PostgreSQL is doing it right

[Hans Schou]

2017-12-21 12:41 GMT+01:00 Rakesh Kumar :

> Could it be that the tool you are using in Oracle is doing commit while
> exiting out due to Deadlock, because there is no explicit rollback.
>

The tool Im using is "sqlplus". By default you are always in a transaction
and auto-commit only occur on exit.
Please note that Oracle leave the table with a half transaction, i.e. only
one row is updated.

Re: Deadlock with one table - PostgreSQL is doing it right

[Hans Schou]

2017-12-21 17:46 GMT+01:00 Jeremy Finzel :

> It's hard to follow how the 2 videos relate, because you don't run the
> same SQL both places.  You first update where i = 2 in Postgres and i = 1
> in Oracle.
>

Well OK. I made a new one for PostgreSQL: https://youtu.be/En8EFv90yCc
Now with same background color.

Re: [v10] CREATE TEMP FUNCTION/CREATE FUNCTION PG_TEMP.X

[Vincenzo Romano]

2017-12-21 17:56 GMT+01:00 Tom Lane :
> Vincenzo Romano  writes:
>> 2017-12-21 17:52 GMT+01:00 Tom Lane :
>>> You have to schema-qualify the temp function name when calling it, too.
>
>> So search_path is not used with functions?
>
> pg_temp is explicitly ignored when searching for functions/operators.
> Otherwise, installing a trojan horse is just too easy.
>
> regards, tom lane

I'm not sure whether this decision actually makes PG more scure.
But, anyway, thanks for the insight: I've just found the
documentations for this.

-- 
Vincenzo Romano - NotOrAnd.IT
Information Technologies
--
NON QVIETIS MARIBVS NAVTA PERITVS

Re: [v10] CREATE TEMP FUNCTION/CREATE FUNCTION PG_TEMP.X

[Tom Lane]

Vincenzo Romano  writes:
> 2017-12-21 17:52 GMT+01:00 Tom Lane :
>> You have to schema-qualify the temp function name when calling it, too.

> So search_path is not used with functions?

pg_temp is explicitly ignored when searching for functions/operators.
Otherwise, installing a trojan horse is just too easy.

regards, tom lane

Re: [v10] CREATE TEMP FUNCTION/CREATE FUNCTION PG_TEMP.X

[Vincenzo Romano]

2017-12-21 17:52 GMT+01:00 Tom Lane :
> Vincenzo Romano  writes:
>> It seems I cannot use a temporary function.
>
> You have to schema-qualify the temp function name when calling it, too.
>
> regards, tom lane

Hi.
So search_path is not used with functions?

-- 
Vincenzo Romano - NotOrAnd.IT
Information Technologies
--
NON QVIETIS MARIBVS NAVTA PERITVS

Re: [v10] CREATE TEMP FUNCTION/CREATE FUNCTION PG_TEMP.X

[Tom Lane]

Vincenzo Romano  writes:
> It seems I cannot use a temporary function.

You have to schema-qualify the temp function name when calling it, too.

regards, tom lane

Re: Deadlock with one table - PostgreSQL is doing it right

[Jeremy Finzel]

It's hard to follow how the 2 videos relate, because you don't run the same
SQL both places.  You first update where i = 2 in Postgres and i = 1 in
Oracle.

On Thu, Dec 21, 2017 at 4:37 AM, Hans Schou  wrote:

> Hi
>
> FYI - if it has any interest
>
> During my preparation for describing what happens when two processes
> update the same row in a table, I came across that PostgreSQL is doing
> right and Oracle is doing it wrong.
>
> The situation is a process which get a deadlock, but because it is a
> script, it sends a commit anyway. This is bad behavior by humans but that's
> how they are.
>
> After both processes commit's the table should be:
>  i |  n
> ---+---
>  1 | 11
>  2 | 21
> in Oracle it is:
>  i |  n
> ---+---
>  1 | 11
>  2 | 22
>
> PostgreSQL: https://youtu.be/rH-inFRMcvQ
> Oracle: https://youtu.be/l2IGoaWql64
>
> PostgreSQL:
> A
> select * from t;
> begin;
> update t set n=n+1 where i=2;
>
> B
> begin;
> update t set n=n+1 where i=1;
> update t set n=n+1 where i=2;
>
> A
> update t set n=n+1 where i=1;
>
> B
> commit;
>
> A
> commit;
>
> best regards
> hans
>

[v10] CREATE TEMP FUNCTION/CREATE FUNCTION PG_TEMP.X

[Vincenzo Romano]

Hi all.
It seems I cannot use a temporary function.
I know there's no "CREATE TEMP FUNCTION".
But while I can do

tmp2=# CREATE FUNCTION pg_temp.x( OUT b BOOL )
language PLPGSQL
AS $L0$
BEGIN
  b := TRUE;
END;
$L0$;
SET search_path TO pg_temp,"$user", public;

the following fails:

tmp2=# SELECT * FROM x();
LINE 1: select * from x();
  ^
HINT:  No function matches the given name and argument types. You
might need to add explicit type casts.
tmp2=# \df+ x

  List of functions
Schema | Name | Result data type | Argument data types | Type |
Volatility | Parallel | Owner | Security | Access privileges |
Language | Source code | Description
+--+--+-+--++--+---+--+---+--+-+-
(0 rows)

tmp2=# \df+ pg_temp.x

  List of functions
Schema | Name | Result data type | Argument data types | Type |
Volatility | Parallel | Owner | Security | Access privileges |
Language | Source code | Description
+--+--+-+--++--+---+--+---+--+-+-
(0 rows)

but this succeeds:

tmp2=# select * from pg_temp.x();
 b
---
 t
(1 row)

I think I am doing/thinking something wrong.
But what?

TALIA!

-- 
Vincenzo Romano - NotOrAnd.IT
Information Technologies
--
NON QVIETIS MARIBVS NAVTA PERITVS

Re: problems with postgresql 10.1 hba_conf on fedora 27

[Matt Zagrabelny]

On Wed, Dec 20, 2017 at 9:30 PM, support-tiger 
wrote:

>
> No, the docs for understanding  hba_conf are not good (yes we can read and
> are fairly smart)  - we made suggestions the last time for several case
> examples and were ignored - okay, simplicity of  pouchdb/couchdb  is
> getting our attention
>
>
Can you point to a link where the hba docs are suboptimal and suggest to
the mailing list an improvement?

-m

Re:

[Andreas Kretschmer]

Am 21.12.2017 um 07:31 schrieb Ramar Duraisamy:

Hi friends,

This is Ramar and i have accidentally deleted database in postgresql
through pg admin.

My backup restore not working.


Can you explain that in more detail? How did you take the backup, how 
did you tried the restore, which error-message occurs?
Please provide also more details, for instance operating system and 
pg-version.




Can i recover my deleted database in postgresql.


The usual way is restore from Backup.


Regards, Andreas

--
2ndQuadrant - The PostgreSQL Support Company.
www.2ndQuadrant.com

Re: Dynamic Enum?

[Olivier Macchioni]

> On 21 Dec 2017, at 15:17, Melvin Davidson  wrote:
> 
> On Thu, Dec 21, 2017 at 5:34 AM, Olivier Macchioni 
> mailto:olivier.macchi...@wingo.ch>> wrote:
> Hello,
> 
> First post on the list, please be indulgent :)
> 
> [...]
> 
> 
> >- use ENUMs to reduce the storage space
> 
> First, please include the PostgreSQL version and O/S when communicating with 
> this list
> 


Thank you for this feedback,

Linux Debian. Currently PostgreSQL version 9.4 but migrating to a newer version 
would not be an issue.

Olivier


signature.asc
Description: Message signed with OpenPGP using GPGMail

Re: Dynamic Enum?

[Melvin Davidson]

On Thu, Dec 21, 2017 at 5:34 AM, Olivier Macchioni <
olivier.macchi...@wingo.ch> wrote:

> Hello,
>
> First post on the list, please be indulgent :)
>
> I'm working on a fairly large DB (for me) - 23 GB data, 42 GB indexes, 100
> M rows for my biggest table - and trying to find possible optimisations on
> the storage requirements... and hopefully trying to keep more indexes in
> RAM.
>
> This DB is actually the import of "events" on a sliding window of 30 days,
> and I have no control over the events which are generated. I have control
> of the import script though.
>
> Many of the columns have the following characteristics:
> - VARCHAR
> - low cardinality (typically < 100 distinct values)
> - but I can see new values appearing "at any time" when importing data
> from external systems. I don't expect the cardinality to grow significantly
> though.
>
> The naive storage of those columns is quite demanding when compared to the
> amount of information they carry, and I'm looking at solutions to optimise
> this. Obviously I could:
>
> - use ENUMs to reduce the storage space to 4 bytes on disk (cf
> https://www.postgresql.org/docs/current/static/datatype-enum.html)
> assuming I managed the ENUMs by adding new values when needed. This would
> probably shrink the indexes significantly as well. It may have an impact on
> the comparison of values as well.
>
> - normalize the DB by adding another table and a FOREIGN KEY - the
> management of this table could be done via triggers for instance, with a
> cost in complexity (triggers, applications accessing the DB, ...)
>
> I would find much more elegant to use a datatype where my VARCHARs would
> be be internally stored as a SMALLINT (or similar), indexed as SMALLINT,
> while still being able to be externally seen as if it was a VARCHAR
> (comparison, ORM bindings, ...)
>
> I didn't find any datatype which would work like this :(
>
> Does anyone know of such a solution ?
>
> Thank you,
>
> Olivier
>

>- use ENUMs to reduce the storage space

First, please include the PostgreSQL version and O/S when communicating
with this list

Next, I recommend you avoid ENUMs and instead use Foreign Keys. ENUMs are
old tech from before FK's were available
and are a PIA to manage/maintain. FK's are a lot easier and simpler.

-- 
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

Re: Migration to PGLister - After

[Magnus Hagander]

On Mon, Nov 20, 2017 at 10:09 PM, Magnus Hagander 
wrote:

> On Mon, Nov 20, 2017 at 9:31 PM, Michael Nolan  wrote:
>
>>
>>
>> On Mon, Nov 20, 2017 at 9:45 AM, Stephen Frost 
>> wrote:
>>
>>>
>>> This list has now been migrated to new mailing list software known as
>>> 'PGLister'.  This migration will impact all users of this mailing list
>>> in one way or another.
>>>
>>
>> Is there more information available about PGLister somewhere, ie, is this
>> a list package that other people running mailing lists might be able to
>> use?  A net search for "PGLister" doesn't find much yet.
>>
>>
> Hi!
>
> Please track the thread at https://www.postgresql.org/
> message-id/CABUevEw7Vs9Hpe-9dRWPwXDa4sPuE71BDNk2X8_UOQ%
> 3Dhvf1tnA%40mail.gmail.com for information on that. TL/DR version is that
> right now it's in a closed repo, but it's scheduled to be opened as soon as
> somebody has had the time to go through it and make sure we're not
> accidentally leaking something that shouldn't be leaked.
>
>
That took a lot longer than expected, but at least we've had someone go
through it now. And hopefully nothing was missed :)

For now, there's a mirror pushed to https://gitlab.com/pglister/pglister.
We're happy to take comments and improvements!


-- 
 Magnus Hagander
 Me: https://www.hagander.net/ 
 Work: https://www.redpill-linpro.com/ 

RE: Foreign Data Wrapper

[Virendra Shaktawat - Quipment India]

Hi Atri,

odbc_fdw

Regards,
Virendra Shaktawat
Database Developer

R&D .Net Department
T / +91 (0)  90999 07116
virendra.shaktawat@ / www.quipment.in

From: Atri Sharma [mailto:atri.j...@gmail.com]
Sent: 21 December 2017 06:21 PM
To: Virendra Shaktawat - Quipment India 
Cc: pgsql-gene...@postgresql.org
Subject: Re: Foreign Data Wrapper

Which FDW?



On Thu, Dec 21, 2017 at 5:54 PM, Virendra Shaktawat - Quipment India 
mailto:virendra.shakta...@quipment.nl>> wrote:
[Quipment Logo]

Hello ,

I have stuck at foreign data wrapper. I am accessing the form MS Sql Server. 
Foreign table has been created with data. unfortunately I am unable to perform 
DML operation like insert, update and delete on foreign table. Whenever I tried 
to perform DML operation on foreign table we are getting error i.e. “ERROR: 
cannot insert into foreign table "test12"
SQL state: 0A000”

Kindly give me response soon because I am stuck at the middle of the project.

Thanks,

Virendra Shaktawat
Database Developer

R&D .Net Department

T / +91 (0) 9099907116
virendra.shaktawat@ / 
www.quipment.in



QTwee Group BV
KvK Arnhem 60858001
BTW NL854090721B01
mail@ / www.quipment.nl

Hoofdkantoor 
Nijmegen
Kerkenbos 1236 C
6546 BE Nijmegen
Tel.

+31 (0)24 372 47 00

Fax.

+31 (0)24 372 47 07


Quipment India
903-904 Landmark,
Beside Titanium City Centre,
Anandnagar Road, 100ft Ring Road,
Ahmedabad
Gujarat - 380015 - India
Tel. +91 (0) 79 4009 7847




Disclaimer

This e-mail message (including any attachment) is intended only for the 
personal use of the recipient(s) named above. This message is confidential and 
may be legally privileged. If you are not an intended recipient, you may not 
review, copy or distribute this message. If you have received this 
communication in error, please notify us immediately by e-mail and delete the 
original message.





--
Regards,

Atri
l'apprenant

Re: Foreign Data Wrapper

[Atri Sharma]

Which FDW?



On Thu, Dec 21, 2017 at 5:54 PM, Virendra Shaktawat - Quipment India <
virendra.shakta...@quipment.nl> wrote:

> [image: Quipment Logo]
>
> Hello ,
>
>
>
> I have stuck at foreign data wrapper. I am accessing the form MS Sql
> Server. Foreign table has been created with data. unfortunately I am unable
> to perform DML operation like insert, update and delete on foreign table.
> Whenever I tried to perform DML operation on foreign table we are getting
> error i.e. “ERROR: cannot insert into foreign table "test12"
>
> SQL state: 0A000”
>
>
>
> Kindly give me response soon because I am stuck at the middle of the
> project.
>
>
>
> Thanks,
>
> *Virendra Shaktawat*
> *Database Developer*
>
> R&D .Net Department
>
> T / +91 (0) 9099907116 <+91%2090999%2007116>
> virendra.shaktawat@ <%20virendra.shakta...@quipment.in> / www.quipment.in
>
>
>
> *QTwee Group BV*
> KvK Arnhem 60858001
> BTW NL854090721B01
> mail@  / www.quipment.nl
>
> *Hoofdkantoor Nijmegen
> *
> Kerkenbos 1236 C
> 6546 BE Nijmegen
>
> Tel.
>
> +31 (0)24 372 47 00
>
> Fax.
>
> +31 (0)24 372 47 07
>
> *Quipment India*
> 903-904 Landmark,
> Beside Titanium City Centre,
> Anandnagar Road, 100ft Ring Road,
> Ahmedabad
> Gujarat - 380015 - India
> Tel. +91 (0) 79 4009 7847 <+91%2079%204009%207847>
>
> *Disclaimer*
>
> This e-mail message (including any attachment) is intended only for the
> personal use of the recipient(s) named above. This message is confidential
> and may be legally privileged. If you are not an intended recipient, you
> may not review, copy or distribute this message. If you have received this
> communication in error, please notify us immediately by e-mail and delete
> the original message.
>
>
>



-- 
Regards,

Atri
*l'apprenant*

Foreign Data Wrapper

[Virendra Shaktawat - Quipment India]

[Quipment Logo]

Hello ,

I have stuck at foreign data wrapper. I am accessing the form MS Sql Server. 
Foreign table has been created with data. unfortunately I am unable to perform 
DML operation like insert, update and delete on foreign table. Whenever I tried 
to perform DML operation on foreign table we are getting error i.e. "ERROR: 
cannot insert into foreign table "test12"
SQL state: 0A000"

Kindly give me response soon because I am stuck at the middle of the project.

Thanks,

Virendra Shaktawat
Database Developer

R&D .Net Department

T / +91 (0) 9099907116
virendra.shaktawat@ / 
www.quipment.in



QTwee Group BV
KvK Arnhem 60858001
BTW NL854090721B01
mail@ / www.quipment.nl

Hoofdkantoor Nijmegen
Kerkenbos 1236 C
6546 BE Nijmegen
Tel.

+31 (0)24 372 47 00

Fax.

+31 (0)24 372 47 07


Quipment India
903-904 Landmark,
Beside Titanium City Centre,
Anandnagar Road, 100ft Ring Road,
Ahmedabad
Gujarat - 380015 - India
Tel. +91 (0) 79 4009 7847




Disclaimer

This e-mail message (including any attachment) is intended only for the 
personal use of the recipient(s) named above. This message is confidential and 
may be legally privileged. If you are not an intended recipient, you may not 
review, copy or distribute this message. If you have received this 
communication in error, please notify us immediately by e-mail and delete the 
original message.

Re: What does tcop stand for?

[Alvaro Herrera]

Tatsuo Ishii wrote:
> >>> currently browsing the source code in src/include/tcop. What does tcop 
> >>> stand for? Can not find any hints in the files. 
> > 
> >>Traffic cop. 
> > 
> > Thanks 
> > cop mean? 
> 
> A cop means a policeman. Probably the name came from that it is
> responsible for the traffic control between frontend and backend.

Traffic control between utility commands and stuff that goes to the
optimizer is how I understand it.

-- 
Álvaro Herrerahttps://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: Deadlock with one table - PostgreSQL is doing it right

[Rakesh Kumar]

Could it be that the tool you are using in Oracle is doing commit while exiting 
out due to Deadlock, because there is no explicit rollback.

Re: psycopg2 and java gssapi questions

[Stephen Frost]

Magnus, Mike,

* Magnus Hagander (mag...@hagander.net) wrote:
> On Wed, Dec 20, 2017 at 8:42 PM, Mike Feld  wrote:
> 
> > Is it possible to authenticate with Postgres from a standalone application
> > using gssapi? In other words, I am able to authenticate with Postgres when
> > a human has logged in to either Windows or Linux and generated a ticket,
> > but is it possible for say a Django site or Java application running on
> > some server somewhere to authenticate with Postgres using gssapi? I realize
> > that psycopg2 has a connection parameter for “krbsrvname”, but how does it
> > generate a ticket? Is this the only alternative to secure authentication
> > since Postgres does not support secure ldap (ldaps)?
> 
> Sure it is.

Yup.

> libpq won't generate the initial ticket, though. The way to do it is to
> have your django or whatever application run "kinit" for the user before it
> starts. This will request a TGT, and the ticket will be present in that
> users environment, and will be used by the libpq client. (it might look
> slightly different for a Java client, but the principle is the same)

You would actually want to use a keytab and then kstart/k5start to make
sure that you've always got a valid ticket.  Just doing a kinit would
mean that the TGT will eventually expire and cause connections to fail.

Thanks!

Stephen


signature.asc
Description: Digital signature

Re: psycopg2 and java gssapi questions

[Dave Cramer]

On 21 December 2017 at 05:27, Magnus Hagander  wrote:

>
>
> On Wed, Dec 20, 2017 at 8:42 PM, Mike Feld  wrote:
>
>> Is it possible to authenticate with Postgres from a standalone
>> application using gssapi? In other words, I am able to authenticate with
>> Postgres when a human has logged in to either Windows or Linux and
>> generated a ticket, but is it possible for say a Django site or Java
>> application running on some server somewhere to authenticate with Postgres
>> using gssapi? I realize that psycopg2 has a connection parameter for
>> “krbsrvname”, but how does it generate a ticket? Is this the only
>> alternative to secure authentication since Postgres does not support secure
>> ldap (ldaps)?
>>
>
> Sure it is.
>
> libpq won't generate the initial ticket, though. The way to do it is to
> have your django or whatever application run "kinit" for the user before it
> starts. This will request a TGT, and the ticket will be present in that
> users environment, and will be used by the libpq client. (it might look
> slightly different for a Java client, but the principle is the same)
>
>
JDBC docs on GSSAPI can be found
https://jdbc.postgresql.org/documentation/head/connect.html


Dave Cramer

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

[no subject]

[Ramar Duraisamy]

Hi friends,

This is Ramar and i have accidentally deleted database in postgresql
through pg admin.

My backup restore not working.

Can i recover my deleted database in postgresql.

Anyone having solution kindly reply me.

Thanks & Regards,

Ramar  D

Deadlock with one table - PostgreSQL is doing it right

[Hans Schou]

Hi

FYI - if it has any interest

During my preparation for describing what happens when two processes update
the same row in a table, I came across that PostgreSQL is doing right and
Oracle is doing it wrong.

The situation is a process which get a deadlock, but because it is a
script, it sends a commit anyway. This is bad behavior by humans but that's
how they are.

After both processes commit's the table should be:
 i |  n
---+---
 1 | 11
 2 | 21
in Oracle it is:
 i |  n
---+---
 1 | 11
 2 | 22

PostgreSQL: https://youtu.be/rH-inFRMcvQ
Oracle: https://youtu.be/l2IGoaWql64

PostgreSQL:
A
select * from t;
begin;
update t set n=n+1 where i=2;

B
begin;
update t set n=n+1 where i=1;
update t set n=n+1 where i=2;

A
update t set n=n+1 where i=1;

B
commit;

A
commit;

best regards
hans

Dynamic Enum?

[Olivier Macchioni]

Hello,

First post on the list, please be indulgent :)

I'm working on a fairly large DB (for me) - 23 GB data, 42 GB indexes, 100 M 
rows for my biggest table - and trying to find possible optimisations on the 
storage requirements... and hopefully trying to keep more indexes in RAM.

This DB is actually the import of "events" on a sliding window of 30 days, and 
I have no control over the events which are generated. I have control of the 
import script though.

Many of the columns have the following characteristics:
- VARCHAR
- low cardinality (typically < 100 distinct values)
- but I can see new values appearing "at any time" when importing data from 
external systems. I don't expect the cardinality to grow significantly though.

The naive storage of those columns is quite demanding when compared to the 
amount of information they carry, and I'm looking at solutions to optimise 
this. Obviously I could:

- use ENUMs to reduce the storage space to 4 bytes on disk (cf 
https://www.postgresql.org/docs/current/static/datatype-enum.html 
) assuming I 
managed the ENUMs by adding new values when needed. This would probably shrink 
the indexes significantly as well. It may have an impact on the comparison of 
values as well.

- normalize the DB by adding another table and a FOREIGN KEY - the management 
of this table could be done via triggers for instance, with a cost in 
complexity (triggers, applications accessing the DB, ...)

I would find much more elegant to use a datatype where my VARCHARs would be be 
internally stored as a SMALLINT (or similar), indexed as SMALLINT, while still 
being able to be externally seen as if it was a VARCHAR (comparison, ORM 
bindings, ...)

I didn't find any datatype which would work like this :(

Does anyone know of such a solution ?

Thank you,

Olivier


signature.asc
Description: Message signed with OpenPGP using GPGMail

Re: psycopg2 and java gssapi questions

[Magnus Hagander]

On Wed, Dec 20, 2017 at 8:42 PM, Mike Feld  wrote:

> Is it possible to authenticate with Postgres from a standalone application
> using gssapi? In other words, I am able to authenticate with Postgres when
> a human has logged in to either Windows or Linux and generated a ticket,
> but is it possible for say a Django site or Java application running on
> some server somewhere to authenticate with Postgres using gssapi? I realize
> that psycopg2 has a connection parameter for “krbsrvname”, but how does it
> generate a ticket? Is this the only alternative to secure authentication
> since Postgres does not support secure ldap (ldaps)?
>

Sure it is.

libpq won't generate the initial ticket, though. The way to do it is to
have your django or whatever application run "kinit" for the user before it
starts. This will request a TGT, and the ticket will be present in that
users environment, and will be used by the libpq client. (it might look
slightly different for a Java client, but the principle is the same)

-- 
 Magnus Hagander
 Me: https://www.hagander.net/ 
 Work: https://www.redpill-linpro.com/