[GENERAL] Constraint exclusion involving joins
Hi, I understand that the constraints exclusion will work only on constant values. But in my case, I will never pass a constant value to the partitioning key when we query the partition table. Will the partition be still be beneficial in this case. If yes, can you please explain Thanks
Re: [GENERAL] Question about paritioning
So is date_trunc better than to_char ? I'm thinking it probably is
as for the number of partitions, well we don't plan on deleting anything,
but from my reading as long as the queries stay on a small amount of parts
that we should be okay.
A
On 27 July 2017 at 15:33, John R Pierce wrote:
> On 7/26/2017 10:08 PM, Alex Samad wrote:
>
>> I have a large table about 3B rows, that I would like to partition on a
>> column called _received which is of type timestamp
>>
>>
> a good goal is to have no more than about 100 partitions max, and ideally
> more like 25.
>
> when we partition on time stamp, we typically do it by the week, as we're
> doing 6 month data retention.
>
> IIRC, we're using DATE_TRUNC('week', timestamp)::DATE for use as the
> partition label and key.
>
>
>
> --
> john r pierce, recycling bits in santa cruz
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
Re: [GENERAL] Developer GUI tools for PostgreSQL
On 26.07.17 - 18:41, Tiffany Thang wrote: > Hi, > I'm new to PostgreSQL. I'm looking for a developer tool that works > similarly to TOAD or SQL Developer for Oracle/MySQL which would allow me to > view and make DDL changes to database objects and create data models. It > would be a plus if I can use the same tool to perform some database > administration tasks. I've grown quite fond of pgModeler: https://www.pgmodeler.com.br/ It's working really nice, has some layout validation checks, good visualization features, easy to use and if you really need you can also put in some SQL statements by hand. It lacks however some administration capabilities so it's best used with pgAdmin (or use the pgsql cli). Cheers, Thore -- signature.asc Description: PGP signature
Re: [GENERAL] Row based permissions: at DB or at Application level?
Am 25.07.2017 um 12:59 schrieb vinny: On 2017-07-25 11:40, Thomas Güttler wrote: I would like to reduce the "ifing and elsing" in my python code (less conditions, less bugs, more SQL, more performance) Regards, Thomas Güttler A quick brainstorm: You could, probably... but you'd have to create a separate database user for every Django user, get Django to connect to the database as that user and setup policies for each of those users, for every use-case. Yes, this could be done. ... I am unsure When I look at an example policy from the manual: CREATE POLICY fp_u ON information FOR UPDATE USING (group_id <= (SELECT group_id FROM users WHERE user_name = current_user)); I'm not sure if this is any less bug-sensitive than an IF in Python... Somehow I trust set operations more then "if" and "else" in a programming language. And don't forget you have to interpret any error-response from the database into something that Django can make understandable to the end-user. Yes? An internal server error is an internal server error. I don't think that you can create anything understandable. You can reply "We are sorry". But maybe I misunderstood what you mean with "error-response from the database". I'm not saying row-level security is bad, far from it, but I doubt that using it to replace Django's own security is going to magically make life much easier. My current concer: I want a SELECT statement wich returns all rows a user is allowed to see. This mean all conditions in my python/django code won't help me. I need a way to create a WHERE clause for this. If I need this in a WHERE clause, then I don't want to have two implementations (once in python, once in SQL-WHERE clause). How to create the WHERE clause is a different topic. I like the django ORM filter methods very much. Next thing is where to apply the WHERE. I could create it in django, or use PG feature "Row Security Policies" ... Using Django-ORM-Filter-methods in "Row Security Policies" would be cool ... This is brainstorming and I am just trying to widen my horizont. Feedback welcome! Regards, Thomas Güttler -- Thomas Guettler http://www.thomas-guettler.de/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Question about paritioning
On 7/27/2017 12:43 AM, Alex Samad wrote: ... as long as the queries stay on a small amount of parts that we should be okay. thats true as long as the planner can restrict the queries to the correct partition... but there's lots of queries that end up having to hit all partitions because the planner can't guess correctly. -- john r pierce, recycling bits in santa cruz -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Row based permissions: at DB or at Application level?
On 2017-07-27 10:27, Thomas Güttler wrote: Am 25.07.2017 um 12:59 schrieb vinny: On 2017-07-25 11:40, Thomas Güttler wrote: I would like to reduce the "ifing and elsing" in my python code (less conditions, less bugs, more SQL, more performance) When I look at an example policy from the manual: CREATE POLICY fp_u ON information FOR UPDATE USING (group_id <= (SELECT group_id FROM users WHERE user_name = current_user)); I'm not sure if this is any less bug-sensitive than an IF in Python... Somehow I trust set operations more then "if" and "else" in a programming language. I understand the feeling, but realistically; you're doing exactly the same, just in a different syntax. And don't forget you have to interpret any error-response from the database into something that Django can make understandable to the end-user. But maybe I misunderstood what you mean with "error-response from the database". Indeed you did :-) row-level security will cause the database to start returning permission-denied messages, for example: (taken from the manual) postgres=> update passwd set shell = '/bin/xx'; ERROR: new row violates WITH CHECK OPTION for "passwd" Your application will have to be able to translate that error into something that the user can understand. In this case it should be something like "Sorry, this password is not allowed". My current concer: I want a SELECT statement wich returns all rows a user is allowed to see. Sure, but the permissions in your application are not just like "John can see rows 1-4 and Pete can see rows that have isVisible=True" In a forum you may have a rule that says that posting new topics is only allowed if you have posted more than ten replies, and the forum allows now posts and the user is not banned. So the row-level permission has to check the user and the forum to decide what to do, and those rulings cannot be done using row-level security so you will have to write pgsql functions that do those checks on those records and well the whol row-level thing turns into a group of functions very quickly. This is brainstorming and I am just trying to widen my horizont. Feedback welcome! Ditto, I'd love to hear input from others! Regards, Thomas Güttler -- Thomas Guettler http://www.thomas-guettler.de/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] upsert: is there a shortcut?
Hello,
it seems not, but I feel like asking.
Is there a way to express a statement like "if you have a conflict on
insert replace all the values in the conflicting record" without
specifying all the fields explicitly? I.e. in a replication system
(where occasional accidents mean replication may restart slightly
before what's already on the target) I generate statements like:
insert into "order_log" ("id","cr_date","order_id","message")
values (%s, %s, %s, %s)
on conflict ("id") do update
set ("cr_date","order_id","message") =
(excluded."cr_date",excluded."order_id",excluded."message")
Is there a way to avoid replicating the list of fields and use instead
something like (new.*) = (excluded.*) as one could do in a trigger?
(that would also imply an (id = excluded.id but it seems harmless).
It seems to me an use case common enough that some syntactic help...
would help. "do update *"? "do update (target.*) = (excluded.*)"?
-- Daniele
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] RETURNS SETOF RECORD with 1 column
Hi all. Please, see this fragment: DROP TABLE IF EXISTS atable; CREATE TABLE atable ( atext TEXT, anint INT ); TRUNCATE atable; INSERT INTO atable VALUES ( 'one',1 ), ( 'two',2 ), ( 'three',3 ), ( 'four',4 ) ; CREATE OR REPLACE FUNCTION afun1( OUT ot TEXT ) RETURNS SETOF RECORD LANGUAGE PLPGSQL AS $l0$ BEGIN RETURN QUERY SELECT atext FROM atable; END; $l0$; CREATE OR REPLACE FUNCTION afun2( OUT ot TEXT, OUT oi INT ) RETURNS SETOF RECORD LANGUAGE PLPGSQL AS $l0$ BEGIN RETURN QUERY SELECT atext,anint FROM atable; END; $l0$; I am not allowed to create afun1() but can create and use afun2(). The error message for afun1() reads: ERROR: function result type must be text because of OUT parameters The idea I understand is that a RECORD must have no less than TWO columns. Of course it's not a problem to declare "RETURNS SETOF TEXT", but why forcing me to "deviate" from a very general construct for a 1 column case? Is this a bug/enhancement or am I missing something? TALIA! -- Vincenzo Romano - NotOrAnd.IT Information Technologies -- NON QVIETIS MARIBVS NAVTA PERITVS -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Interesting streaming replication issue
On Wed, Jul 26, 2017 at 11:55 PM, James Sewell wrote: > On Thu, Jul 27, 2017 at 4:41 PM, Gunnar "Nick" Bluth < > gunnar.bl...@pro-open.de> wrote: > >> >> are you sure you're scp'ing from the archive, not from pg_xlog? >> > > Yes: > > restore_command = 'scp -o StrictHostKeyChecking=no > 10.154.19.30:/archive/xlog//%f > %p' > > Although you are right - that would almost make sense if I had done that! > Sounds a lot like a cleanup process on your archive directory or something getting in the way. Are the logs pg is asking for in that archive dir?
Re: [GENERAL] RETURNS SETOF RECORD with 1 column
Vincenzo Romano writes: > CREATE OR REPLACE FUNCTION afun1( OUT ot TEXT ) > RETURNS SETOF RECORD > The error message for afun1() reads: > ERROR: function result type must be text because of OUT parameters Indeed. It's trying to protect you from simple typos. If you want a consistent syntax I'd suggest CREATE OR REPLACE FUNCTION afun1() RETURNS TABLE (ot text) ... It's still really "setof text" under the hood. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] RETURNS SETOF RECORD with 1 column
2017-07-27 16:03 GMT+02:00 Tom Lane : Thanks a lot for your reply with valuable details. > Vincenzo Romano writes: >> CREATE OR REPLACE FUNCTION afun1( OUT ot TEXT ) >> RETURNS SETOF RECORD > >> The error message for afun1() reads: >> ERROR: function result type must be text because of OUT parameters > > Indeed. It's trying to protect you from simple typos. Which typo? It looks it's preventing me from consistently using another simple syntax for FRSes. CREATE OR REPLACE FUNCTION afun1( OUT ot TEXT ) RETURNS SETOF TEXT ... CREATE OR REPLACE FUNCTION afun2( OUT ot TEXT, OUT it INT ) RETURNS SETOF RECORD ... CREATE OR REPLACE FUNCTION afun3( OUT ot TEXT, OUT it INT, OUT on NUMERIC ) RETURNS SETOF RECORD ... CREATE OR REPLACE FUNCTION afun4( OUT ot TEXT, OUT it INT, OUT on NUMERIC, OUT od DATE ) RETURNS SETOF RECORD ... This means that RETURNS SETOF RECORD syntax is only available from 2 columns on. While RETURNS TABLE() syntax is always available, *also* for 1 column. I thought that "RETURNS SETOF RECORD" was meant for FRSes just like RETURN TABLE() is. Why not, then? > If you want a consistent syntax I'd suggest > > CREATE OR REPLACE FUNCTION afun1() RETURNS TABLE (ot text) ... Indeed. The main difference is that with RETURNS SETOF RECORD I still get the "usual"(tm) function argument list in the usual place: between two parentheses. It's a matter of style. And a consistent one. But I still don't get the point for not having it for a single column. Surely it's my fault. > It's still really "setof text" under the hood. > > regards, tom lane -- Vincenzo Romano - NotOrAnd.IT Information Technologies -- NON QVIETIS MARIBVS NAVTA PERITVS -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Developer GUI tools for PostgreSQL
Thanks everyone for your input! On Thu, Jul 27, 2017 at 3:48 AM, Thore Boedecker wrote: > On 26.07.17 - 18:41, Tiffany Thang wrote: > > Hi, > > I'm new to PostgreSQL. I'm looking for a developer tool that works > > similarly to TOAD or SQL Developer for Oracle/MySQL which would allow me > to > > view and make DDL changes to database objects and create data models. It > > would be a plus if I can use the same tool to perform some database > > administration tasks. > > I've grown quite fond of pgModeler: > https://www.pgmodeler.com.br/ > > It's working really nice, has some layout validation checks, good > visualization features, easy to use and if you really need you can > also put in some SQL statements by hand. > > It lacks however some administration capabilities so it's best used > with pgAdmin (or use the pgsql cli). > > Cheers, > Thore > > -- >
Re: [GENERAL] Developer GUI tools for PostgreSQL
On 2017-07-27 00:41, Tiffany Thang wrote: Hi, I'm new to PostgreSQL. I'm looking for a developer tool that works similarly to TOAD or SQL Developer for Oracle/MySQL which would allow me to view and make DDL changes to database objects and create data models. It would be a plus if I can use the same tool to perform some database administration tasks. So far, I've found TOra and pgAdmin 4. Are there any other popular GUI tools? Thanks in advance. There is also DbSchema, for getting a good overview of your tables. I use Jetbrains dataGrip for maintenance etc. I find that using a GUI for things like creating functions is nice but you never really learn what's going on so you are in trouble when you have to do something and your GUI is not available. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Indexes being ignored after upgrade to 9.5
On Tue, Jul 25, 2017 at 10:34 PM, Nick Brennan wrote: > Hi, > > We have recently promoted our Prod DB slave (2TB) to migrate to new > hardware, and upgraded from v9.2.9.21 to 9.5.1.6 using pg_upgrade. > > > The upgrade went without incident and we have been running for a week, but > the optimizer is ignoring indexes on 2 of our largest partitioned tables > causing very slow response times. > > > The indexes are Btree indexes on BIGINT columns, which the optimizer used > to return queries with ms response times on 9.2. Post-upgrade the queries > sequential scan and do not use indexes unless we force them. > Can you show the explain (analyze) plans for both forcing and non-forcing? And with both 9.5 and the old 9.2, if that is still available. > > We've added duplicate indexes and analyzing, however the new indexes are > still ignored unless we force using enable_seqscan=no or reduce > random_page_cost to 2. The query response times using the new indexes are > still as slow when we do this. > Still as slow as what? As slow as when you use the seq scan, or as slow as when you used index scans back under 9.2, or as slow as the the non-duplicate indexes were? Cheers, Jeff
Re: [GENERAL] Indexes being ignored after upgrade to 9.5
Hi Peter, Many thanks for your response. I tried to cancel the thread, it was unfortunately stupidity that was the issue. We'd been forced to manually analyze our tables due to time constraints, and one of the table partitions read in the query was missed. It was reporting a bitmap index scan on the parent so we thought all was ok, and was then causing other tables to sequential scan. A further misunderstanding was that an explain analyze would initiate stats gathering on all queried tables, however this is not the case. Thanks again for your response, we'll check the behaviour you report. Best regards Nick > On 27 Jul 2017, at 00:40, Peter Geoghegan wrote: > >> On Wed, Jul 26, 2017 at 2:05 PM, Peter Geoghegan wrote: >>> On Tue, Jul 25, 2017 at 10:34 PM, Nick Brennan wrote: >>> We've added duplicate indexes and analyzing, however the new indexes are >>> still ignored unless we force using enable_seqscan=no or reduce >>> random_page_cost to 2. The query response times using the new indexes are >>> still as slow when we do this. Checking pg_stat_user_indexes the number of >>> tuples returned per idx_scan is far greater after the upgrade than before. >>> All indexes show valid in pg_indexes. > > I assume that you mean that pg_stat_user_indexes.idx_tup_read is a lot > higher than before, in proportion to pg_stat_user_indexes.idx_scan. > What about the ratio between pg_stat_user_indexes.idx_tup_read and > pg_stat_user_indexes.idx_tup_fetch? How much has that changed by? > > -- > Peter Geoghegan -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Monitoring of a hot standby with a largely idle master
On Wed, Jul 26, 2017 at 1:44 AM, Michael Paquier wrote: > On Mon, Jul 24, 2017 at 9:08 PM, Jeff Janes wrote: > > On Sun, Jul 16, 2017 at 8:47 AM, Michael Paquier < > michael.paqu...@gmail.com> > > wrote: > >> What do you think about the patch attached? > > > > Looks OK. Should it mention specifically "On a hot standby" rather than > "On > > a standby"? Otherwise people might be left confused on how they are > > supposed to do this on a generic standby. It is the kind of thing which > is > > obvious once you know it, but confusing the first time you encounter it. > > Yes, right. Let's update as you suggest. > new version looks good. Thanks, Jeff
Re: [GENERAL] Interesting streaming replication issue
> > >> >>> are you sure you're scp'ing from the archive, not from pg_xlog? >>> >> >> Yes: >> >> restore_command = 'scp -o StrictHostKeyChecking=no >> 10.154.19.30:/archive/xlog//%f >> %p' >> >> Although you are right - that would almost make sense if I had done that! >> > > Sounds a lot like a cleanup process on your archive directory or something > getting in the way. Are the logs pg is asking for in that archive dir? > That's the strange thing - if you look at the log not only are they there, the standby has already retrieved them. It's then asking for the log again via the stream. -- James Sewell, PostgreSQL Team Lead / Solutions Architect Suite 112, Jones Bay Wharf, 26-32 Pirrama Road, Pyrmont NSW 2009 *P *(+61) 2 8099 9000 *W* www.jirotech.com *F * (+61) 2 8099 9099 -- -- The contents of this email are confidential and may be subject to legal or professional privilege and copyright. No representation is made that this email is free of viruses or other defects. If you have received this communication in error, you may not copy or distribute any part of it or otherwise disclose its contents to anyone. Please advise the sender of your incorrect receipt of this correspondence.
Re: [GENERAL] Index Only Scan and Heap Fetches
On Tue, Jul 18, 2017 at 7:21 AM, Mikhail wrote: > Hi guys, > > I'm running the process, that executes "select * from sr where sr.id=210 > for update;", then some calculations and finally "update sr set usage = > where sr.id = 210;". That operation is done in a loop. > > In parallel session i'm running the query: > > test=# explain (analyze, buffers) select id from sr where id = 210; > QUERY PLAN > > > -- > > Index Only Scan using sr_pk on sr (cost=0.57..8.59 rows=1 width=4) (actual > time=0.018..1.172 rows=1 loops=1) >Index Cond: (id = 210) >Heap Fetches: 10 >Buffers: shared hit=592 > Planning time: 0.057 ms > Execution time: 1.183 ms > Running that several times I can see, that the number of "Heap Fetches" is > varying in some range (from 1 to ~80-100), sequentaly growing till > ~(80-100) than starting from 1. > Considering that the autovacuum process is turned off (for research > purposes only :) ), I was expecting the infinite growth of Heap > Fetches since no cleaning of dead rows or visibility map support occurs. > > Can someone explain, what else can decrease the number of heap access > needed to check the rows visibility? > Btree indexes have a micro-vacuum feature. If you visit a heap tuple based on reference from an index tuple, and find that the heap tuple is dead-to-all, then when you get back to the index you can kill that index's reference to the heap tuple. Future accesses via that same index for the same tuple then no longer need to visit the heap. Cheers, Jeff
Re: [GENERAL] Question about paritioning
so I have a pgsql function that will create tables based on MD for x years in advance and inherits of base table. with this CHECK (_received >= '2013-01-01 00:00:00+11'::timestamp with time zone AND _received < '2013-02-01 01:00:00+11'::timestamp with time zone) now for the insert, do I create / update this monthly have had a 2 or 3 level if then check before inserting or do I create a programatic insert that works out the table name On 27 July 2017 at 18:36, John R Pierce wrote: > On 7/27/2017 12:43 AM, Alex Samad wrote: > >> >> ... as long as the queries stay on a small amount of parts that we should >> be okay. >> > > thats true as long as the planner can restrict the queries to the correct > partition... but there's lots of queries that end up having to hit all > partitions because the planner can't guess correctly. > > > > -- > john r pierce, recycling bits in santa cruz > > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >
[GENERAL] Why am I getting doubles?
Hi, The query below should get foreign keys for a specific table: draft=# SELECT DISTINCT kcu.ordinal_position AS ordinal, kcu.position_in_unique_constraint AS position, tc.constraint_name AS name, tc.constraint_schema AS schema, tc.table_name AS table, kcu.column_name AS column, ccu.table_name AS tableName, ccu.column_name AS columnName, rc.update_rule, rc.delete_rule FROM information_schema.table_constraints tc, information_schema.key_column_usage kcu, information_schema.constraint_column_usage ccu, information_schema.referential_constraints rc WHERE tc.constraint_name = kcu.constraint_name AND ccu.constraint_name = tc.constraint_name AND rc.constraint_name = tc.constraint_name AND constraint_type = 'FOREIGN KEY' AND tc.constraint_schema = 'public' AND tc.table_name = 'leaguescorehitter'; ordinal | position | name | schema | table | column |tablename| columnname | update_rule | delete_rule -+--+++---+--+-++-+- 2 |2 | leaguescorehitter_id_fkey1 | public | leaguescorehitter | playerid | playersinleague | id | NO ACTION | NO ACTION 1 |1 | leaguescorehitter_id_fkey1 | public | leaguescorehitter | id | playersinleague | id | NO ACTION | NO ACTION 1 |1 | leaguescorehitter_id_fkey1 | public | leaguescorehitter | id | playersinleague | playerid | NO ACTION | NO ACTION 1 |1 | leaguescorehitter_scoreid_fkey | public | leaguescorehitter | scoreid | scorehits | scoreid| NO ACTION | NO ACTION 1 |1 | leaguescorehitter_id_fkey | public | leaguescorehitter | id | leagues | id | NO ACTION | NO ACTION 2 |2 | leaguescorehitter_id_fkey1 | public | leaguescorehitter | playerid | playersinleague | playerid | NO ACTION | NO ACTION (6 rows) draft=# \d leaguescorehitter Table "public.leaguescorehitter" Column | Type | Modifiers --+-+--- id | integer | playerid | integer | scoreid | integer | value| numeric | Indexes: "leaguescorehitter_playerid" btree (playerid) Foreign-key constraints: "leaguescorehitter_id_fkey" FOREIGN KEY (id) REFERENCES leagues(id) "leaguescorehitter_id_fkey1" FOREIGN KEY (id, playerid) REFERENCES playersinleague(id, playerid) "leaguescorehitter_scoreid_fkey" FOREIGN KEY (scoreid) REFERENCES scorehits(scoreid) If I don't have a foreing key with 2 fields everything works fine. Is there a reason I'm seeing duplicate records on the query above? Thank you. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Why am I getting doubles?
Igor Korot writes: > Is there a reason I'm seeing duplicate records on the query above? Your example isn't complete, but I think the problem is your WHERE clause isn't equating enough columns. For instance, if I do db=# create table pp(f1 int, f2 int, primary key (f1,f2)); CREATE TABLE that produces two rows in information_schema.key_column_usage: db | public| pp_pkey | db| public | pp | f1 |1 | db | public| pp_pkey | db| public | pp | f2 |2 | Your WHERE clause can't tell the difference between these. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Developer GUI tools for PostgreSQL
I think it's funny that after all these years pgadmin3 is still the most comprehensive GUI for postgres. Even though it's prone to crashing on my machine and I have paid for datagrip I still reach for it first. On Fri, Jul 28, 2017 at 2:46 AM, vinny wrote: > On 2017-07-27 00:41, Tiffany Thang wrote: > >> Hi, >> I'm new to PostgreSQL. I'm looking for a developer tool that works >> similarly to TOAD or SQL Developer for Oracle/MySQL which would allow >> me to view and make DDL changes to database objects and create data >> models. It would be a plus if I can use the same tool to perform some >> database administration tasks. >> >> So far, I've found TOra and pgAdmin 4. Are there any other popular GUI >> tools? >> >> Thanks in advance. >> > > There is also DbSchema, for getting a good overview of your tables. > I use Jetbrains dataGrip for maintenance etc. I find that using a GUI for > things like creating functions is nice > but you never really learn what's going on so you are in trouble when you > have to do something and your GUI is not available. > > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >
Re: [GENERAL] RETURNS SETOF RECORD with 1 column
On Thursday, July 27, 2017, Vincenzo Romano wrote: > > The main difference is that with RETURNS SETOF RECORD I still get the > "usual"(tm) function argument list in the usual place: between two > parentheses. > It's a matter of style. And a consistent one. > But I still don't get the point for not having it for a single column. > > Docs say: " When there are OUT or INOUT parameters, the RETURNS clause can be omitted. ". Sounds like you should you do just that. David J.
Re: [GENERAL] RETURNS SETOF RECORD with 1 column
On Thursday, July 27, 2017, David G. Johnston wrote: > On Thursday, July 27, 2017, Vincenzo Romano > wrote: >> >> The main difference is that with RETURNS SETOF RECORD I still get the >> "usual"(tm) function argument list in the usual place: between two >> parentheses. >> It's a matter of style. And a consistent one. >> But I still don't get the point for not having it for a single column. >> >> > Docs say: " When there are OUT or INOUT parameters, the RETURNS clause > can be omitted. ". Sounds like you should you do just that. > > Except you'd have no where to put the "setof" modifier...So, yeah, you probably aren't going to personal style preference catered to here. David J.
Re: [GENERAL] Developer GUI tools for PostgreSQL
On 2017-07-28 06:31, Tim Uckun wrote: I think it's funny that after all these years pgadmin3 is still the most comprehensive GUI for postgres. Have you looked at EMS SQL-Manager, I don't remember PgAdmin having any where near hte features that it has :-P Even though it's prone to crashing on my machine and I have paid for datagrip I still reach for it first. It depends entirely on your personal preference, not so much on the features of the tool, there are whole subcultures in the IT world who swear by VIM. I tend to swear *at* VIM. But in the end it's personal preference and requirements that decide which is the best tool. I use DbSchema because of how quickly and visually I can create tables, but I use DataGrip to execute queries to actually manage a database, create functions, views etc. Most tools have free preview licences so download them try them out, see what feels good to you. Just remember that a tool is not a substitute for knowledge, knowing where to click in a GUI is not the same as knowing how to maintain a database. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
