Re: Rationale for aversion to the central database?

[Steven Lembark]

On Sat, 28 Apr 2018 08:02:21 +0200
"Peter J. Holzer"  wrote:

> On 2018-04-27 22:52:39 +, g...@luxsci.net wrote:
> > Perhaps I'm extreme. In my ideal world, developers might not even
> > know table names! I'm kidding ,sorta...  
> 
> If they don't know the table names, how can they write those stored
> procedures?

One of the main reasons DBA's need to be pigs. Much spaghetti can be
avoided in ORM frameworks with well-designed views. 

-- 
Steven Lembark   1505 National Ave
Workhorse Computing Rockford, IL 61103
lemb...@wrkhors.com+1 888 359 3508

Re: Rationale for aversion to the central database?

[Steven Lembark]

> That seems un-pragmatic to me. IMHO if any business logic needs
> access to lots of data, it's best implemented by code that
> resides in the database itself, close to the data. I once had a

There is a balance: ETL and reporting code is often iterative
and can be more flexable in an external language. 

Getting the balance right takes some thought, planning -- both of
which are usually in short supply on software projects. 

Testing database code and structures is easy in PG using TAP:



Also easy to test database contents and emedded logic:



Putting all or no logic into the database as a knee-jerk reaction 
is usually a mistake. Especially with PG testing is easy using 
extensions and schemas to segregate the code/data being tested.

At some point the data will have to hit code, especially in ETL or
reporting logic. Balancing the work in and out of the database
just makes sense. Which is probaly why it doesn't happen...


-- 
Steven Lembark   1505 National Ave
Workhorse Computing Rockford, IL 61103
lemb...@wrkhors.com+1 888 359 3508

Query function arg data types ONLY (no arg names)

[Jeremy Finzel]

It appears that neither pg_get_function_arguments
nor pg_get_function_identity_arguments could be used for this.  I want to
get function argument data types from the catalog by ordinal position,
without the argument name.

For example, I want the same information for these 2 functions:

foo(p_1 int, p_2 text)

- {int, text}

foo(int, text)

- {int, text}

Any suggestions as to how to use the catalogs or built-in postgres
functions to query this?

Thank you!
Jeremy

Re: Query function arg data types ONLY (no arg names)

[Pavel Stehule]

Hi

2018-04-28 18:52 GMT+02:00 Jeremy Finzel :

> It appears that neither pg_get_function_arguments
> nor pg_get_function_identity_arguments could be used for this.  I want to
> get function argument data types from the catalog by ordinal position,
> without the argument name.
>
> For example, I want the same information for these 2 functions:
>
> foo(p_1 int, p_2 text)
>
> - {int, text}
>
> foo(int, text)
>
> - {int, text}
>
> Any suggestions as to how to use the catalogs or built-in postgres
> functions to query this?
>

 CREATE OR REPLACE FUNCTION public.foo(a integer, b integer, c text)
 RETURNS text
 LANGUAGE sql
AS $function$ select 'hi'; $function$

postgres=# select (proargtypes::regtype[])[0:] from pg_proc where proname =
'foo';
┌─[ RECORD 1 ]┬┐
│ proargtypes │ {integer,integer,text} │
└─┴┘

Regards

Pavel


> Thank you!
> Jeremy
>

Re: Query function arg data types ONLY (no arg names)

[Tom Lane]

Jeremy Finzel  writes:
> It appears that neither pg_get_function_arguments
> nor pg_get_function_identity_arguments could be used for this.  I want to
> get function argument data types from the catalog by ordinal position,
> without the argument name.

Something involving pg_proc.proargtypes::regtype[] might be useful.

regards, tom lane

downgrade postgis 2.4.3 to 2.4.1

[Jonas Pedersen]

Hi list



I have recently upgraded my PostGIS 2.4.1 to 2.4.3, which introduced a
problem caused by one of the dependencies (geos 3.5.0 -> 3.6.2).



Now I would like to do a yum history undo to return my system to its
previous state, but when invoking yum history undo on that particular
transaction id, I get the following messages:



Failed to downgrade: postgis24_10-2.4.1-1.rhel7.x86_64

Failed to downgrade: postgis24_10-client-2.4.1-1.rhel7.x86_64

Failed to downgrade: postgis24_10-devel-2.4.1-1.rhel7.x86_64

Failed to downgrade: postgis24_10-utils-2.4.1-1.rhel7.x86_64

Failed to downgrade: postgis24_96-2.4.1-1.rhel7.x86_64

Failed to downgrade: postgis24_96-client-2.4.1-1.rhel7.x86_64



I guess this is because the packages in question doesn’t exists in the repo
anymore.



Is there any way to get access to these packages again?



PS I’m running Red Hat 7.4



Regards,

Jonas




-- 
Jonas Pedersen
Øresundsvej 47A, 4th
2300 København S
Denmark
Tlf. +45 51212034
Skype name. jp2650

Re: Rationale for aversion to the central database?

[Peter J. Holzer]

On 2018-04-28 09:54:27 -0500, Steven Lembark wrote:
> On Sat, 28 Apr 2018 08:02:21 +0200
> "Peter J. Holzer"  wrote:
> 
> > On 2018-04-27 22:52:39 +, g...@luxsci.net wrote:
> > > Perhaps I'm extreme. In my ideal world, developers might not even
> > > know table names! I'm kidding ,sorta...  
> > 
> > If they don't know the table names, how can they write those stored
> > procedures?
> 
> One of the main reasons DBA's need to be pigs. Much spaghetti can be
> avoided in ORM frameworks with well-designed views. 

Apparently my remark was too short (and perhaps with too little context)
to be clear.

As I understood g...@luxsci.net, they were arguing for writing lots of
stored procedures so that developers wouldn't even have to know the
table names (they would just call the procedures) [Although I now see
that it was somebody else who proposed that as the only interface].

But those stored procedures don't write themselves. Somebody has to
write them and a person who writes code is called a developer. So there
needs to be at least one developer who knows the table names - the
developer of the stored procedures. And that developer should better be
good - API design is hard, and if you take away SQL from your fellow
developers you should give them something better (for the task at hand),
not something worse.

hp

-- 
   _  | Peter J. Holzer| we build much bigger, better disasters now
|_|_) || because we have much more sophisticated
| |   | h...@hjp.at | management tools.
__/   | http://www.hjp.at/ | -- Ross Anderson 


signature.asc
Description: PGP signature

Re: Rationale for aversion to the central database?

[g...@luxsci.net]

 
 
On April 28, 2018 11:18:02 am PDT, "Peter J. Holzer"  wrote:

On 2018-04-28 09:54:27 -0500, Steven Lembark wrote:

On Sat, 28 Apr 2018 08:02:21 +0200
"Peter J. Holzer"  wrote:

> On 2018-04-27 22:52:39 +, g...@luxsci.net wrote:
> > Perhaps I'm extreme. In my ideal world, developers might not even
> > know table names! I'm kidding ,sorta... 
>
> If they don't know the table names, how can they write those stored
> procedures?

One of the main reasons DBA's need to be pigs. Much spaghetti can be
avoided in ORM frameworks with well-designed views.


Apparently my remark was too short (and perhaps with too little context)
to be clear.

As I understood g...@luxsci.net, they were arguing for writing lots of
stored procedures so that developers wouldn't even have to know the
table names (they would just call the procedures) [Although I now see
that it was somebody else who proposed that as the only interface].

But those stored procedures don't write themselves. Somebody has to
write them and a person who writes code is called a developer. So there
needs to be at least one developer who knows the table names - the
developer of the stored procedures. And that developer should better be
good - API design is hard, and if you take away SQL from your fellow
developers you should give them something better (for the task at hand),
not something worse.

===

Yes, sorry, I meant application developers. Well, actually I meant 
developers who only look at the db through an ORM and who like it that 
way. At my current position, the ones called "developers" all use 
Django, and they practically never look at ( let alone interact with) 
the db using psql. Forget about writing and using pgsql functions. 
There are no DBAs or database developers. I am trying to change this 
culture a bit but I may give up soon. Tragic, really, given the mostly 
terrible performance that they get with naive Django code. Some of 
these developers have even gone so far as to proclaim that PG is too 
slow ( they didn't even configure it !) for their "big" data, which 
really aren't big, and move on to something like ES, which they really 
don't need. For example, they have code that could benefit from simple 
FTS operations, and instead of taking seconds or minutes, could be done 
in milliseconds. Or perhaps they could not execute certain queries for 
user interfaces quickly
enough with their Django code so they think that they need ES for those 
queries. So now they have to manage and design their ES 
implementations, activities which frankly should _not_ be taken 
lighlty, and manage their data in the PG db which they eventually come 
back to for at least some sane data integrity. Now they have to perform 
very long and convoluted processes to keep both in sync. Ouch. Know 
your tools!


But there is no all or nothing, as others have pointed out. Some things 
are better done and more easily managed outside of the DB, at least for 
me.
For example, I tried doing lots of bigint math in pgsql and I moved to 
external C code for that.
That C code still called tried and true pgsql convenience functions for 
getting at the correct data however, and these were the same functions 
that my Java code used for other activities.


Gerry

Re: pg_stat_statements : how to catch non successfully finished statements ?

[legrand legrand]

OK I see ...

This hook should be used only for ERROR (no WARNING nor NOTICE ...)
and the only real interesting information is  
  ErrorData -> internalquery; /* text of internally-generated query */

This doesn't permit to (re)build the link to queryid 
(that is based on parse tree, but not available here)

So the only solution is to had queryId to ErrorData in this hook
or create a new hook fired on ERROR and containing queryId ?

Regards
PAscal



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

Re: pg_stat_statements : how to catch non successfully finished statements ?

[Tom Lane]

legrand legrand  writes:
> So the only solution is to had queryId to ErrorData in this hook
> or create a new hook fired on ERROR and containing queryId ?

I see no particular need for a new hook.  What's needed here is for
pgss_ExecutorRun (and maybe some other existing functions in
pg_stat_statements) to go ahead and record the statement when they catch
an error thrown out of standard_ExecutorRun, rather than just updating
the module's nested_level variable and re-throwing.

The hard part here is that you have to be really careful what you do in
a PG_CATCH block, because the only thing you know for sure about the
backend's state is that it's not good.  Catalog fetches are right out,
and anything that might itself throw an error had best be avoided as
well.  (Which, among other things, means that examining executor state
would be a bad idea, and I'm not even sure you'd want to traverse the plan
tree.)

I'm not convinced that it's practical for pg_stat_statements to make a new
shared hashtable entry under those constraints.  But figuring out how to
minimize the risks around that is the stumbling block, not lack of a hook.

regards, tom lane

Re: Query function arg data types ONLY (no arg names)

[Jeremy Finzel]

On Sat, Apr 28, 2018 at 12:01 PM Pavel Stehule 
wrote:

> Hi
>
> 2018-04-28 18:52 GMT+02:00 Jeremy Finzel :
>
>> It appears that neither pg_get_function_arguments
>> nor pg_get_function_identity_arguments could be used for this.  I want to
>> get function argument data types from the catalog by ordinal position,
>> without the argument name.
>>
>> For example, I want the same information for these 2 functions:
>>
>> foo(p_1 int, p_2 text)
>>
>> - {int, text}
>>
>> foo(int, text)
>>
>> - {int, text}
>>
>> Any suggestions as to how to use the catalogs or built-in postgres
>> functions to query this?
>>
>
>  CREATE OR REPLACE FUNCTION public.foo(a integer, b integer, c text)
>  RETURNS text
>  LANGUAGE sql
> AS $function$ select 'hi'; $function$
>
> postgres=# select (proargtypes::regtype[])[0:] from pg_proc where proname
> = 'foo';
> ┌─[ RECORD 1 ]┬┐
> │ proargtypes │ {integer,integer,text} │
> └─┴┘
>
> Regards
>
> Pavel
>
>
>> Thank you!
>> Jeremy
>>
>
This is perfect - thank you!