Re: [GENERAL] STABLE vs. IMMUTABLE w.r.t. indexes

*care *if the GUC value changes after the database session has begun. I want it to get the value and substitute it out in the planning phase so that the planner can find and use the correct index. Is there any way to do this without marking something immutable when it is not? Thanks. Moshe

[GENERAL] STABLE vs. IMMUTABLE w.r.t. indexes

res should know that it's not going to change over the course of the query, couldn't it substitute the value as well? More details can be provided on request. Thanks. Moshe Jacobson Principal Architect, Nead Werx Inc. <http://www.neadwerx.com> 2323 Cumberland Parkway · Suite 20

Re: [GENERAL] How can I tell if pg_restore is running?

command. This also means that I can't rely on a wrapper to pg_restore, as Keith suggested earlier. Moshe Jacobson Manager of Systems Engineering, Nead Werx Inc. <http://www.neadwerx.com> 2323 Cumberland Parkway · Suite 201 · Atlanta, GA 30339 "Quality is not an act, it is a habit." -- Aristotle

[GENERAL] How can I tell if pg_restore is running?

the database has been restored and is running. Is there a way for my extension's trigger functions to return immediately when triggered by pg_restore? Thanks. Moshe Jacobson Manager of Systems Engineering, Nead Werx Inc. <http://www.neadwerx.com> 2323 Cumberland Parkway · Suite 201 ·

Re: [GENERAL] Optimizer issue -- bad query plan?

ss ON el_fss.location = r.location AND el_fss.role = 35 LEFT JOIN tb_project_participant pp_ipm ON pp_ipm.project = r.project AND pp_ipm.role = 3; Moshe Jacobson Manager of Systems Engineering, Nead Werx Inc. <http://www.neadwerx.com> 2323 Cumberland Parkway · Suite 201 · Atlanta, GA 30339 “Qua

Re: [GENERAL] Optimizer issue -- bad query plan?

stgreSQL_9.0 > Read the section named "Join Removal". > Right, I'm aware of this feature -- I'm just curious why it's not working! Moshe Jacobson Manager of Systems Engineering, Nead Werx Inc. <http://www.neadwerx.com> 2323 Cumberland Parkway · Suite 201 ·

Re: [GENERAL] Optimizer issue -- bad query plan?

the tables needed for the columns I wasn't referencing at all. I thought it might be that postgres couldn't optimize the query due to the fact that it was a view, so that's why I pulled out this portion to see if it would speed up. But it didn't. Moshe Jacobson Manager of Sys

[GENERAL] Optimizer issue -- bad query plan?

of the tables in the query are being joined despite the fact that they are not needed at all. Why is this? Moshe Jacobson Manager of Systems Engineering, Nead Werx Inc. <http://www.neadwerx.com> 2323 Cumberland Parkway · Suite 201 · Atlanta, GA 30339 “Quality is not an act, it is a

[GENERAL] Need pg_dump not to dump extension-created triggers

iday about this very extension. Come say hi! Moshe Jacobson Manager of Systems Engineering, Nead Werx Inc. <http://www.neadwerx.com> 2323 Cumberland Parkway · Suite 201 · Atlanta, GA 30339 "Quality is not an act, it is a habit." -- Aristotle

Re: [GENERAL] Passing dynamic parameters to a table-returning function

This should be the example used in the docs for LATERAL JOIN rather than the contrived, rather useless example that is there today. Moshe Jacobson Manager of Systems Engineering, Nead Werx Inc. <http://www.neadwerx.com> 2323 Cumberland Parkway · Suite 201 · Atlanta, GA 30339 "Qualit

Re: [GENERAL] CentOS 6 and Postgresql 9.3.4 from PGDG

I know that on my system, all of the postgres binaries and libs are linked through /etc/alternatives. Moshe Jacobson Manager of Systems Engineering, Nead Werx Inc. <http://www.neadwerx.com> 2323 Cumberland Parkway · Suite 201 · Atlanta, GA 30339 "Quality is not an act, it is a habit." -- Aristotle

Re: [GENERAL] CentOS 6 and Postgresql 9.3.4 from PGDG

versions installed simultaneously for upgrade purposes. I just create symlinks data/ and backups/ in /var/lib/pgsql to point to the dirs of the same names under the 9.3/ directory. Moshe Jacobson Manager of Systems Engineering, Nead Werx Inc. <http://www.neadwerx.com> 2323 Cumberland Parkway ·

Re: [GENERAL] Database Design: Maintain Audit Trail of Changes

ust >> what you're looking for. >> > > Do you plan to support primary keys other than single column integers? > I may modify it to support bigints, but I don't have any plans to support multi-column primary keys. It would require too much overhaul of the code. Moshe J

Re: [GENERAL] Database Design: Maintain Audit Trail of Changes

tware) Moshe Jacobson Manager of Systems Engineering, Nead Werx Inc. <http://www.neadwerx.com> 2323 Cumberland Parkway · Suite 201 · Atlanta, GA 30339 "Quality is not an act, it is a habit." -- Aristotle On Thu, Jan 3, 2013 at 10:38 AM, Rich Shepard wrote: > I have the need to dev

Re: [GENERAL] Wanted: ALTER TRIGGER ... OWNED BY EXTENSION

or just > shut up and drop the triggers. > I would love for DROP EXTENSION to shut up and drop the triggers, but I'm not sure how to accomplish that without manually manipulating pg_depend (and even then I'm not sure if that'll do it). Suggestions welcome. Thanks. Moshe J

Re: [GENERAL] Wanted: ALTER TRIGGER ... OWNED BY EXTENSION

On Mon, Mar 31, 2014 at 4:32 PM, Moshe Jacobson wrote: > There is a trigger function on the extension's config table that creates > the trigger functions and installs them (both from the same function). I am > about to try playing with pg_depend to see if it has the desired effect

Re: [GENERAL] Wanted: ALTER TRIGGER ... OWNED BY EXTENSION

ns, the per-table pg_trigger records)? > There is a trigger function on the extension's config table that creates the trigger functions and installs them (both from the same function). I am about to try playing with pg_depend to see if it has the desired effect. Moshe Jacobson Manager of

[GENERAL] Wanted: ALTER TRIGGER ... OWNED BY EXTENSION

7;d really like to be able to mark my extension's triggers as owned by the extension, so that these errors will not show up when restoring. Is there a better workaround that I'm not aware of? Thanks. Moshe Jacobson Manager of Systems Engineering, Nead Werx Inc. <http://www.neadwerx.co

[GENERAL] Help with exclusion constraint

the constraint. I'm not sure what to do about this. Any help would be appreciated. Moshe Jacobson Manager of Systems Engineering, Nead Werx Inc. <http://www.neadwerx.com> 2323 Cumberland Parkway · Suite 201 · Atlanta, GA 30339 "Quality is not an act, it is a habit." -- Aristotle

Re: [GENERAL] Help with exclusion constraint

ool_col) WHERE bool_col > IS TRUE; > Ahh yes, why didn't I think of that? Thank you. Moshe Jacobson Manager of Systems Engineering, Nead Werx Inc. <http://www.neadwerx.com> 2323 Cumberland Parkway · Suite 201 · Atlanta, GA 30339 "Quality is not an act, it is a habit." -- Aristotle

Re: [GENERAL] Help with exclusion constraint

E (fk_col WITH =) WHERE (bool_col); Yes, that would do the trick! I think I'll just set up a partial unique index as per Igor's suggestion, however. Thank you! Moshe Jacobson Manager of Systems Engineering, Nead Werx Inc. <http://www.neadwerx.com> 2323 Cumberland Parkway · Suite 201 · A

Re: [GENERAL] question on writing a function

nactive users, deletes their data and sets them to Deleted. Moshe Jacobson Manager of Systems Engineering, Nead Werx Inc. <http://www.neadwerx.com> 2323 Cumberland Parkway · Suite 201 · Atlanta, GA 30339 "Quality is not an act, it is a habit." -- Aristotle

Re: [GENERAL] Suddenly all tables were gone

conversion to 9.3, then I was using pg_dump. Also what database where you trying to connect to, the one in 9.1 or 9.3? > 9.3. The db was fine on 9.1. Moshe Jacobson Manager of Systems Engineering, Nead Werx Inc. <http://www.neadwerx.com> 2323 Cumberland Parkway · Suite 201 · Atlanta, G

Re: [GENERAL] Suddenly all tables were gone

tabase to test some things, and I seem to recall that it was working fine. Moshe Jacobson Manager of Systems Engineering, Nead Werx Inc. <http://www.neadwerx.com> 2323 Cumberland Parkway · Suite 201 · Atlanta, GA 30339 "Quality is not an act, it is a habit." -- Aristotle

Re: [GENERAL] Suddenly all tables were gone

necting as postgres, no special search_path, same config was working earlier. No harm in asking! Moshe Jacobson Manager of Systems Engineering, Nead Werx Inc. <http://www.neadwerx.com> 2323 Cumberland Parkway · Suite 201 · Atlanta, GA 30339 "Quality is not an act, it is a habit." -- Aristotle

Re: [GENERAL] Suddenly all tables were gone

index: 32 Maximum size of a TOAST chunk:1996 Date/time type storage: 64-bit integers Float4 argument passing: by value Float8 argument passing: by value Data page checksum version: 0 Moshe Jacobson Manager of Systems Engineering, Nead Werx I

[GENERAL] Suddenly all tables were gone

this happen before? Thanks. Moshe Jacobson Manager of Systems Engineering, Nead Werx Inc. <http://www.neadwerx.com> 2323 Cumberland Parkway · Suite 201 · Atlanta, GA 30339 "Quality is not an act, it is a habit." -- Aristotle

[GENERAL] Announce: Cyan Audit: Forensic audit logging for PostgreSQL

ut this is the first release to the public. I welcome and appreciate any feedback, praise or constructive criticism. Thank you. Moshe Jacobson Manager of Systems Engineering, Nead Werx Inc. <http://www.neadwerx.com> 2323 Cumberland Parkway · Suite 201 · Atlanta, GA 30339 "Quali

Re: [GENERAL] pg_dump dumps EVENT TRIGGER owned by extension

On Mon, Dec 30, 2013 at 2:02 PM, Tom Lane wrote: > I've committed fixes for these Will these fixes appear in 9.3.3? Thanks. Moshe Jacobson Manager of Systems Engineering, Nead Werx Inc. <http://www.neadwerx.com> 2323 Cumberland Parkway · Suite 201 · Atlanta, GA 30339 &quo

Re: [GENERAL] pg_dump dumps EVENT TRIGGER owned by extension

x27;ve committed fixes for these, but if you have an existing > extension that should have an event trigger as member, you'll need to use > ALTER EXTENSION ADD EVENT TRIGGER to clean up the mess. > Thank you, Tom. Moshe Jacobson Manager of Systems Engineering, Nead Werx Inc. <h

[GENERAL] pg_dump dumps EVENT TRIGGER owned by extension

e the event trigger already exists. Is this a bug? Moshe Jacobson Manager of Systems Engineering, Nead Werx Inc. <http://www.neadwerx.com> 2323 Cumberland Parkway · Suite 201 · Atlanta, GA 30339 "Quality is not an act, it is a habit." -- Aristotle

Re: [GENERAL] Bug? Function with side effects not evaluated in CTE

( select ss.space_sku from tb_space_sku ss left join tt_space_skus tt on tt.space_sku = ss.space_sku where tt.space_sku is null and ss.space = $pk_space ) delete from tb_space_sku ss using tt_space_skus_to_delete tt where ss.space = $pk_space and ss.space_sku = tt

Re: [GENERAL] Bug? Function with side effects not evaluated in CTE

ction, then the CTE query should always be evaluated just like CREATE TEMP TABLE. There is no question as to how many times to evaluate it here. It is just once. Moshe Jacobson Manager of Systems Engineering, Nead Werx Inc. <http://www.neadwerx.com> 2323 Cumberland Parkway · Suite 201

Re: [GENERAL] Bug? Function with side effects not evaluated in CTE

of the belief that if the function in a CTE is volatile, that it should be executed unconditionally. Moshe Jacobson Manager of Systems Engineering, Nead Werx Inc. <http://www.neadwerx.com> 2323 Cumberland Parkway · Suite 201 · Atlanta, GA 30339 "Quality is not an act, it is a habit." -- Aristotle

Re: [GENERAL] Bug? Function with side effects not evaluated in CTE

By the way, I am running Postres 9.1.9.

[GENERAL] Bug? Function with side effects not evaluated in CTE

. Would someone please look into this? Thanks. Moshe Jacobson Manager of Systems Engineering, Nead Werx Inc. <http://www.neadwerx.com> 2323 Cumberland Parkway · Suite 201 · Atlanta, GA 30339 “Quality is not an act, it is a habit.” — Aristotle

[GENERAL] Inconsistent behavior with unnamed columns

Why does bool get special treatment? postgres# select 'abc', 1, false; ?column? | ?column? | bool --+--+-- abc |1 | f (1 row) Moshe Jacobson Nead Werx, Inc. | Manager of Systems Engineering 2323 Cumberland Parkway, Suite 201 | Atlanta, G

Re: [GENERAL] psql swallowed my "BEGIN;" on reset... user beware?

ute further commands on a line of input if any of them fails due to a terminated connection. Moshe Jacobson Nead Werx, Inc. | Manager of Systems Engineering 2323 Cumberland Parkway, Suite 201 | Atlanta, GA 30339 mo...@neadwerx.com | www.neadwerx.com "Quality is not an act, it is a habit." -- Aristotle

[GENERAL] Rename extension?

How can I do this? Thanks. Moshe Jacobson Nead Werx, Inc. | Manager of Systems Engineering 2323 Cumberland Parkway, Suite 201 | Atlanta, GA 30339 mo...@neadwerx.com | www.neadwerx.com "Quality is not an act, it is a habit." -- Aristotle

Re: [GENERAL] How to switch file systems with least downtime?

; Thanks. That's how I normally do it, but I was hoping to eliminate much of the downtime between 4 and 6. Moshe Jacobson Nead Werx, Inc. | Manager of Systems Engineering 2323 Cumberland Parkway, Suite 201 | Atlanta, GA 30339 mo...@neadwerx.com | www.neadwerx.com "Quality is not an act, it is a habit." -- Aristotle

Re: [GENERAL] How to switch file systems with least downtime?

t; to become the master and restarting just that database on port 5432? Not a bad idea, but in this case, a small amount of downtime is preferable over going to that level of effort. Moshe Jacobson Nead Werx, Inc. | Manager of Systems Engineering 2323 Cumberland Parkway, Suite 201 | Atlanta,

[GENERAL] How to switch file systems with least downtime?

igure the first part of this is to do a pg_start_backup() and rsync the files over., bu I'm not sure what to do after that. Thanks for your help. Moshe Jacobson Nead Werx, Inc. | Manager of Systems Engineering 2323 Cumberland Parkway, Suite 201 | Atlanta, GA 30339 mo...@neadwerx.com | www.ne

Re: [GENERAL] pg_extension_config_dump() with a sequence

Thank you very much, Tom. That was it. Our other server is running 9.1.9 and that's why it worked there. On Tue, Aug 27, 2013 at 10:50 PM, Tom Lane wrote: > Moshe Jacobson writes: > > On Tue, Aug 20, 2013 at 7:58 PM, Tom Lane wrote: > >> Well, I think you did it wrong,

Re: [GENERAL] pg_extension_config_dump() with a sequence

ssage from server: ERROR: cannot copy from sequence "sq_pk_myitem" pg_dump: The command was: COPY public.sq_pk_myitem TO stdout; (1)(0j)[jehsom@moshe ~]$ And I got the error here. I'm not sure why this happens because it doesn't happen on another server here. Any hel

Re: [GENERAL] pg_extension_config_dump() with a sequence

ceive the errors above. I didn't drop & recreate the extension to fix it on prod -- I just created a new version of it and updated it. Any ideas? -- Moshe Jacobson Nead Werx, Inc. | Manager of Systems Engineering 2323 Cumberland Parkway, Suite 201 | Atlanta, GA 30339 mo...@neadwerx

[GENERAL] pg_extension_config_dump() with a sequence

"sq_pk_audit_data_type" pg_dump: The command was: COPY auditlog.sq_pk_audit_data_type TO stdout; pg_dumpall: pg_dump failed on database "ises", exiting Why does it work with pg_dump but not pg_dumpall? -- Moshe Jacobson Nead Werx, Inc. | Manager of Systems Engineering 2323 Cumberlan

Re: [GENERAL] odd locking behaviour

id the deadlock (with some amount of > work) but I really don't understand why it behaves as it does. > > > > On Thu, Jul 4, 2013 at 8:40 AM, Moshe Jacobson wrote: > >> Confirmed reproducible on version 9.1 as well. Very odd. >> >> >> On Wed, Jul 3, 20

[GENERAL] Dynamically accessing record elements using EXECUTE

table_name ) || ' WHERE ' || quote_ident( my_table_pk_col_name ) || ' = ' || quote_nullable( in_row_pk ) INTO my_row; EXECUTE 'my_row.' || my_key || ' := ' || my_pk_default; -- Copy the created row back into

Re: [GENERAL] unable to call a function

t archive at Nabble.com. > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- Moshe Jacobson Nead Werx, Inc. | Manager of Systems Engineering 2323 Cumberland Parkway, Suite 201 | Atlanta, GA 30339 mo...@neadwerx.com | www.neadwerx.com "Quality is not an act, it is a habit." -- Aristotle

Re: [GENERAL] odd locking behaviour

east it would be > understandable. > > However, why does it only try to acquire the lock on the second update > > If I do a ROLLBACK in CON1, then I see CON2 finish the UPDATE and it > acquires a > lock on table A. Why? > > Thank you. > > -- Moshe Jacobson Nead Werx, Inc. | Manager of Systems Engineering 2323 Cumberland Parkway, Suite 201 | Atlanta, GA 30339 mo...@neadwerx.com | www.neadwerx.com "Quality is not an act, it is a habit." -- Aristotle

Re: [GENERAL] pg_restore order and check constraints

n > constraints are added via ALTER TABLE. So you suggest I use a trigger instead of a constraint? Thanks -- Moshe Jacobson Nead Werx, Inc. | Manager of Systems Engineering 2323 Cumberland Parkway, Suite 201 | Atlanta, GA 30339 mo...@neadwerx.com | www.neadwerx.com "Quality is not an act, it is a habit." -- Aristotle

[GENERAL] pg_restore order and check constraints

the check constraint because it checks all of the entries when it's applied. Any suggestions? -- Moshe Jacobson Nead Werx, Inc. | Manager of Systems Engineering 2323 Cumberland Parkway, Suite 201 | Atlanta, GA 30339 mo...@neadwerx.com | www.neadwerx.com "Quality is not an act, it

[GENERAL] could not write to hash-join temporary file: No space left on device

My database is total around 100 gigs of data, and I have 50 gigs free on the volume, yet I get this error message. It was a simple join. Is there a way to see more information on why I got this error? -- Moshe Jacobson Nead Werx, Inc. | Manager of Systems Engineering 2323 Cumberland Parkway

Re: [GENERAL] Strange behavior of "=" as assignment operator

ce of "=" and given us an option to enable it if our code broke. Not leaving it in by default. Any PG committers who can change this in 9.3? -- Moshe Jacobson Nead Werx, Inc. | Manager of Systems Engineering 2323 Cumberland Parkway, Suite 201 | Atlanta, GA 30339 mo...@neadwerx.com | www

[GENERAL] Strange behavior of "=" as assignment operator

set some_value = 6 where some_value = 5; UPDATE 1 test=# select modified, some_value from foo; modified | some_value + 2013-05-28 15:44:51.082989 | 6 (1 row) -- Moshe Jacobson Nead Werx, Inc. | Manager of Systems Engineering 2323 Cum

Re: [GENERAL] search_path for database vs user vs session

On Tue, May 28, 2013 at 2:41 AM, Jov wrote: > you can use the psql \drds command: Perfect! Just what I was looking for. Thanks Jov, and thanks to Joe Conway as well for a different solution. Moshe -- Moshe Jacobson Nead Werx, Inc. | Manager of Systems Engineering 2323 Cumberland Park

Re: [GENERAL] search_path for database vs user vs session

On Mon, May 27, 2013 at 3:07 PM, Moshe Jacobson wrote: > I'd like to know how to see the search_path setting attached to a > particular user/role independent of the session. > Oh, and I'd also like to see the current setting of the database so I know what will happen if I cle

Re: [GENERAL] search_path for database vs user vs session

org/**docs/9.2/static/functions-**info.html<http://www.postgresql.org/docs/9.2/static/functions-info.html> Adrian, I'd like to know how to see the search_path setting attached to a particular user/role independent of the session. Thanks. -- Moshe Jacobson Nead Werx, Inc. | Manager of Sys

[GENERAL] search_path for database vs user vs session

how search_path; search_path --- public, audit_log (1 row) Thanks. -- Moshe Jacobson Nead Werx, Inc. | Manager of Systems Engineering 2323 Cumberland Parkway, Suite 201 | Atlanta, GA 30339 mo...@neadwerx.com | www.neadwerx.com "Quality is not an act, it is a habit." -- Aristotle

[GENERAL] 9.1: Slow to add range check on indexed column

constraint on an indexed column. Is this correct? Is this addressed in future releases? Thanks. -- Moshe Jacobson Nead Werx, Inc. | Manager of Systems Engineering 2323 Cumberland Parkway, Suite 201 | Atlanta, GA 30339 mo...@neadwerx.com | www.neadwerx.com "Quality is not an act, it is a

Re: [GENERAL] Strange locking problem

Solution: The inserts of the foreign key to tb_entity were blocking the updates to those rows of tb_entity. I solved the problem by making the foreign key constraints deferrable and deferring checking on them till the end of the transaction. On Tue, May 21, 2013 at 3:24 PM, Moshe Jacobson

Re: [GENERAL] Strange locking problem

On Tue, May 21, 2013 at 2:39 PM, Moshe Jacobson wrote: > What could be causing this ROW SHARE lock to be in place for some cloning > operations and not others? Perhaps there is a way to see which specific > statement is causing the lock to occur, but I'm not sure how to look this

[GENERAL] Strange locking problem

t I'm not sure how to look this up. Any help would be appreciated. Thanks! -- Moshe Jacobson Nead Werx, Inc. | Manager of Systems Engineering 2323 Cumberland Parkway, Suite 201 | Atlanta, GA 30339 mo...@neadwerx.com | www.neadwerx.com "Quality is not an act, it is a habit." -- Aristotle

Re: [GENERAL] How to clone a running master cluster?

That option doesn't seem to exist, but pg_basebackup does seem to be the way to go. Thanks for the help, Jeff! On Sat, May 11, 2013 at 2:15 PM, Jeff Janes wrote: > On Sat, May 11, 2013 at 7:56 AM, Moshe Jacobson wrote: > >> I have a master database cluster on one server, and

[GENERAL] How to clone a running master cluster?

clone my master cluster to another machine, including all of the necessary xlogs, without bringing down the original master or slave? Step-by-step instructions would be much appreciated. Thank you! -- Moshe Jacobson Nead Werx, Inc. | Manager of Systems Engineering 2323 Cumberland Parkway, Suite 20

Re: [GENERAL] Need solution for weekly database "snapshot"

ckup that does not include all of the audit logs. Thanks! -- Moshe Jacobson Nead Werx, Inc. | Manager of Systems Engineering 2323 Cumberland Parkway, Suite 201 | Atlanta, GA 30339 mo...@neadwerx.com | www.neadwerx.com "Quality is not an act, it is a habit." -- Aristotle

Re: [GENERAL] Need solution for weekly database "snapshot"

ew reporting snapshot > > database. > > Setup a slave. > Not bad, but the transaction logs would fill up the file system. Besides, it would not be worth it to set up a whole database cluster just for this purpose. Any alternatives? -- Moshe Jacobson Nead Werx, Inc. | Manager of Systems

[GENERAL] Need solution for weekly database "snapshot"

can be accessed throughout the week? Thanks for any suggestion. -- Moshe Jacobson Nead Werx, Inc. | Manager of Systems Engineering 2323 Cumberland Parkway, Suite 201 | Atlanta, GA 30339 mo...@neadwerx.com | www.neadwerx.com "Quality is not an act, it is a habit." -- Aristotle

Re: [GENERAL] Most efficient way to insert without duplicates

om import i left join persona_followers pf on i.service_id = pf.service_id and i.follower_id = pf.follower_id where pf.service_id is null order by 1,2; This will insert only those rows that are not already present, and involves no subqueries and only one join. -- Moshe Jacobson Nead Werx, Inc.

Re: [GENERAL] Can you spot the difference?

ead to meaningful > statistical changes. > " > > So at a guess there has not been enough churn on the table. > So pg_restore's COPY would not trigger the ANALYZE? That seems wrong. -- Moshe Jacobson Nead Werx, Inc. | Manager of Systems Engineering 2323 Cumberland Parkway, Sui

Re: [GENERAL] Can you spot the difference?

atabase was created & restored days ago, and the autovacuum daemon is running with default settings. Thanks. -- Moshe Jacobson Nead Werx, Inc. | Manager of Systems Engineering 2323 Cumberland Parkway, Suite 201 | Atlanta, GA 30339 mo...@neadwerx.com | www.neadwerx.com "Quality is not an act, it is a habit." -- Aristotle

Re: [GENERAL] Can you spot the difference?

That was it! Thanks Adrian and Tom! On Tue, Apr 16, 2013 at 3:29 PM, Adrian Klaver wrote: > On 04/16/2013 12:07 PM, Moshe Jacobson wrote: > >> Hi PostgreSQL friends, >> >> I have two databases in the same cluster that are almost identical. One >> is a copy of the o

[GENERAL] Can you spot the difference?

tgres@moshe=>devmain:ises_coelacanth=# \d tb_order_location > Table > "public.tb_order_location" >Column|Type | > Modifiers > > -+-

Re: [GENERAL] Passing dynamic parameters to a table-returning function

Perfect, that is exactly what I needed. Thanks David! On Mon, Feb 4, 2013 at 6:49 PM, David Johnston wrote: > Moshe Jacobson wrote > > I'm > > unsure of the syntax for passing in values from columns in the database > as > > the parameters of a set-returning

Re: [GENERAL] Passing dynamic parameters to a table-returning function

looking for simple SQL syntax that can be executed outside of a stored procedure. Moshe -- Moshe Jacobson Nead Werx, Inc. | Manager of Systems Engineering 2323 Cumberland Parkway, Suite 201 | Atlanta, GA 30339 mo...@neadwerx.com | www.neadwerx.com "People don't multitask because they&

Re: [GENERAL] partial time stamp query

-07-07 13:30:00 > etc… > > WHERE derived_tsoil_fifteen_min_stacked.time2 = '2010-07-07*' > but that grabs nothing Just cast the timestamp to a date before you compare it: WHERE derived_tsoil_fifteen_min_stacked.time2::date = '2010-07-07' -- Moshe Jacobson Nead Werx,

[GENERAL] Passing dynamic parameters to a table-returning function

sure of the syntax for passing in values from columns in the database as the parameters of a set-returning function from which I want to select *. Any help would be appreciated. Thanks, Moshe -- Moshe Jacobson Nead Werx, Inc. | Manager of Systems Engineering 2323 Cumberland Parkway, Suite 2

Re: [GENERAL] Optimizing select count query which often takes over 10 seconds

same "yw" value. Then, you might want to create an index on the ranking column as well as the yw column, which you already have indexed. Moshe -- Moshe Jacobson Nead Werx, Inc. | Senior Systems Engineer 2323 Cumberland Parkway, Suite 201 | Atlanta, GA 30339 mo...@neadwerx.com | www.neadwerx.com

Re: [GENERAL] spring a string to rows (Postgresql 8.4)

, string_split_to_row(col1, ','); > > Return: > = > 1, a > 1, b > 1, c > > Thanks alot! > Emi > > > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.or

[GENERAL] Difference between varchar and text?

practical difference between varchar with no limit and text? Thanks, and apologies if this is a FAQ... Moshe -- Moshe Jacobson Nead Werx, Inc. | Senior Systems Engineer 2323 Cumberland Parkway, Suite 201 | Atlanta, GA 30339 mo...@neadwerx.com | www.neadwerx.com

Re: [GENERAL] Delay streaming replication using a paramter

> > This is the reason i want to find out if it is possible to delay > replication incase of any corruption of data not propagating to the slave. > > Thanks > > -- > MAHH > Network/System Analyst > 07788174559 > -- Moshe Jacobson Nead Werx, Inc. | Senior Sys

Re: [GENERAL] Parallel Insert and Delete operation

il the other one is done. > > That is handled by the database system automatically. > > Yours, > Laurenz Albe > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- Moshe Jacobson Nead Werx, Inc. | Senior Systems Engineer 2323 Cumberland Parkway, Suite 201 | Atlanta, GA 30339 mo...@neadwerx.com | www.neadwerx.com

Re: [GENERAL] Extra space when converting number with to_char

t; -- > Samuel Gilbert > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- Moshe Jacobson Nead Werx, Inc. | Senior Systems Engineer 2323 Cumberland Parkway, Suite 201 | Atlanta, GA 30339 mo...@neadwerx.com | www.neadwerx.com

Re: [GENERAL] [pgsql-es-ayuda] como exportar separado por comas una tabla grande

do (killed) > LD_PRELOAD=$PLL LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$PG_BIN_PATH/../lib > "$PG_BIN_PATH/psql.bin" "$@" > > Y obvio el archivo quedo vacío, como podría exportar los datos?? Muchas > gracias lista > > > -- > Cordialmente, > > Ing. Hellmuth I. Vargas S. > > > -- Moshe Jacobson Nead Werx, Inc. | Senior Systems Engineer 2323 Cumberland Parkway, Suite 201 | Atlanta, GA 30339 mo...@neadwerx.com | www.neadwerx.com

Re: [GENERAL] Shorthand syntax for triggers

gt; IF TG_OP = 'DELETE' THEN RENAME OLD TO myrow; > ELSE RENAME NEW TO myrow; END IF; > > and then use > > insert into audits values (myrow.value); Does the RENAME syntax actually work? My understanding is that it has been broken for a long time. -- Moshe Jacobson Nead Werx

Re: [GENERAL] Re: What's faster? BEGIN ... EXCEPTION or CREATE TEMP TABLE IF NOT EXISTS?

this is what I was aiming at, based on the OP mentioning he only > has a limited amount of data to manage in this way. > Yup, I'm going to see how this goes using session variables. I think it is exactly what I need. Thanks! -- Moshe Jacobson Nead Werx, Inc. | Senior Systems Eng

[GENERAL] Counting all results before LIMIT

sults, and we do another identical query using count(*) to get the total count. Is there a more efficient way to do this that does not require us to do two queries? I just feel that it's a waste of resources the way we do it. Thanks! -- Moshe Jacobson Nead Werx, Inc. | Senior System

Re: [GENERAL] Re: What's faster? BEGIN ... EXCEPTION or CREATE TEMP TABLE IF NOT EXISTS?

On Thu, Oct 4, 2012 at 6:12 AM, Ivan Voras wrote: > On 01/10/2012 15:36, Moshe Jacobson wrote: > > I am working on an audit logging trigger that gets called for every row > > inserted, updated or deleted on any table. > > For this, I need to store a couple of temporary sess

Re: [GENERAL] What's faster? BEGIN ... EXCEPTION or CREATE TEMP TABLE IF NOT EXISTS?

do a catalog scan (even if you've established > an exception block the server still internally has to do a catalog > scan in order to raise an appropriate error). The exception block has > the additional overhead of a subtransaction. > OK this makes sense. Thanks :-) I will

Re: [GENERAL] What's faster? BEGIN ... EXCEPTION or CREATE TEMP TABLE IF NOT EXISTS?

not. > > IMO the right way to do it is to generate a unique application token > [...] when your application session logs in. That token should be passed > into > *all* your session specific backend functions [...] > No, this will not work because the backend functions are trigger fu

Re: [GENERAL] What's faster? BEGIN ... EXCEPTION or CREATE TEMP TABLE IF NOT EXISTS?

ms with stale data. I have been unable to find a way to automatically clear that data upon start or end of a session, or at least check if it's been set in this session or not. -- Moshe Jacobson Nead Werx, Inc. | Senior Systems Engineer 2323 Cumberland Parkway, Suite 201 | Atlanta, GA 30339 mo...@neadwerx.com | www.neadwerx.com

[GENERAL] What's faster? BEGIN ... EXCEPTION or CREATE TEMP TABLE IF NOT EXISTS?

every row? Thanks. -- Moshe Jacobson Nead Werx, Inc. | Senior Systems Engineer 2323 Cumberland Parkway, Suite 201 | Atlanta, GA 30339 mo...@neadwerx.com | www.neadwerx.com

Re: [GENERAL] Dropping a column on parent table doesn't propagate to children?

Perfect response, thank you Sergey. On Thu, Aug 30, 2012 at 7:29 AM, Sergey Konoplev < sergey.konop...@postgresql-consulting.com> wrote: > On Wed, Aug 29, 2012 at 5:48 PM, Moshe Jacobson > wrote: > > 1. If I want the inherited table's columns indexed the same way as

Re: [GENERAL] Dropping a column on parent table doesn't propagate to children?

I am specifically asking about NOT using LIKE but just INHERITS. > #2 > I don't use schemas personnally, but if you do, I suggest you test and > see what happens > I will. Also, do you or does anyone know what the asterisk is for, that I asked about at the end of my original p

Re: [GENERAL] Dropping a column on parent table doesn't propagate to children?

On Wed, Aug 29, 2012 at 12:11 PM, Vincent Veyron wrote: > Le mercredi 29 août 2012 à 09:48 -0400, Moshe Jacobson a écrit : > > > Questions: > > 1. If I want the inherited table's columns indexed the same way as the > > parent, must I create new indexes on the inherite

Re: [GENERAL] Dropping a column on parent table doesn't propagate to children?

ables created with pure inheritance as opposed to "like "? Thank you. On Wed, Aug 29, 2012 at 4:35 AM, Sergey Konoplev < sergey.konop...@postgresql-consulting.com> wrote: > On Wed, Aug 29, 2012 at 12:26 AM, Moshe Jacobson > wrote: > > The docs said that the descendant

[GENERAL] Dropping a column on parent table doesn't propagate to children?

orded <= '2012-08-26 14:45:35.989979'::timestamp without time zone) "tb_audit_event_row_op_check" CHECK (row_op = ANY (ARRAY['I'::bpchar, 'U'::bpchar, 'D'::bpchar])) Inherits: tb_audit_event Also, another question about the docs. The syntax for the ALTER TABLE command starts as follows: ALTER TABLE [ ONLY ] name [ * ] What is the asterisk? It is not explained anywhere on that page. Thanks, Moshe -- Moshe Jacobson Nead Werx, Inc. | Senior Systems Engineer 2323 Cumberland Parkway, Suite 201 | Atlanta, GA 30339 mo...@neadwerx.com | www.neadwerx.com

[GENERAL] Renaming tables and their indexes simultaneously

the table, and what might have caused it to stop doing so recently? We are on Postgres 9.1, but we did not recently upgrade... Thanks. -- Moshe Jacobson Nead Werx, Inc. | Senior Systems Engineer 2323 Cumberland Parkway, Suite 201 | Atlanta, GA 30339 mo...@neadwerx.com | www.neadwerx.com

Re: [GENERAL] Best practice non privilege postgres-user

l-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- Moshe Jacobson Nead Werx, Inc. | Senior Systems Engineer 2323 Cumberland Parkway, Suite 201 | Atlanta, GA 30339 mo...@neadwerx.com | www.neadwerx.com

[GENERAL] Revoking table & function creation privileges

> create table tb_foo(i int); CREATE TABLE mjacobson@zeus=>[local]:ises=> drop table tb_foo; DROP TABLE -- Moshe Jacobson Nead Werx, Inc. | Senior Systems Engineer 2323 Cumberland Parkway, Suite 201 | Atlanta, GA 30339 mo...@neadwerx.com | www.neadwerx.com

  1   2   >