Re: [GENERAL] Please say it isn't so

On Tue, Jul 11, 2017 at 9:51 PM, Steve Litt wrote: > Hi all, > > Please tell me this is a mistake: > > https://wiki.postgresql.org/wiki/Systemd > > Why a database system should care about how processes get started is > beyond me. Systemd is an entangled mess that every

Re: [GENERAL] Python versus Other Languages using PostgreSQL

On Mon, May 8, 2017 at 2:26 PM, Paul Hughes wrote: > Hello, > > I noticed that most of the largest web platforms that use PostgreSQL as > their primary database, also use Python as their primary back-end language. > Yet, according to every benchmark I could find over the last

Re: [GENERAL] why isn't this subquery wrong?

On Thu, Apr 20, 2017 at 3:56 PM, jonathan vanasco wrote: > thanks all! > > On Apr 20, 2017, at 6:42 PM, David G. Johnston wrote: > > ​Subqueries can see all columns of the parent. When the subquery actually > uses one of them it is called a "correlated subquery". > > > i

Re: [GENERAL] why isn't this subquery wrong?

On Thu, Apr 20, 2017 at 3:17 PM, jonathan vanasco wrote: > > I ran into an issue while changing a database schema around. Some queries > still worked, even though I didn't expect them to. > > Can anyone explain to me why the following is valid (running 9.6) ? > > schema > >

Re: [GENERAL] Handling psql lost connections

On Wed, Mar 29, 2017 at 9:05 AM, Adrian Klaver <adrian.kla...@aklaver.com> wrote: > On 03/29/2017 08:49 AM, Steve Crawford wrote: > >> When firewalls/VPNs stand between my psql client and a remote PostgreSQL >> server the connection will on occasion time ou

[GENERAL] Handling psql lost connections

When firewalls/VPNs stand between my psql client and a remote PostgreSQL server the connection will on occasion time out and drop. This results in the following scenario: -Leave for lunch mid project - leave psql open. -Return from lunch, complete and submit large query. -Notice query is taking

Re: [GENERAL] CenOS 5/Postgresql 9.6

because those aren't replaced, yet. Cheers, Steve On Sat, Mar 18, 2017 at 3:59 AM, Devrim Gündüz <dev...@gunduz.org> wrote: > > Hi, > > On Fri, 2017-03-17 at 12:15 -0700, Steve Crawford wrote: > > The question remains - does anyone know where I might find packages so I &

Re: [GENERAL] CenOS 5/Postgresql 9.6

On Fri, Mar 17, 2017 at 11:35 AM, John R Pierce <pie...@hogranch.com> wrote: > On 3/17/2017 11:07 AM, Steve Crawford wrote: > >> Where might I find yum repos PostgreSQL 9.6 on CentOS 5 (i386 & x86_64)? >> >> RHEL/CentOS 5 is still in production with extended

[GENERAL] CenOS 5/Postgresql 9.6

Where might I find yum repos PostgreSQL 9.6 on CentOS 5 (i386 & x86_64)? RHEL/CentOS 5 is still in production with extended support through 2020 but seems to be dropped from the 9.6 PGDG repos. Cheers, Steve

Re: [GENERAL] GMT FATAL: remaining connection slots are reserved for non-replication superuser connections, but I'm using pgBouncer for connection pooling

On Sat, Feb 25, 2017 at 4:19 AM, lisandro wrote: > Hi there! Please tell me if this isn't the place to post my question, I'm > new > in the list. > > I'm using PostgreSQL 9.3, I have around 150 databases, and I use pgBouncer > for connection pooling. > My server is a

Re: [GENERAL] postgresql how to duplicate rows in result.

For my enlightenment, why use LATERAL here? I get the same result with a simple CROSS JOIN (though overall I like the clever solution). Cheers, Steve On Thu, Feb 16, 2017 at 12:11 AM, Alessandro Baggi < alessandro.ba...@gmail.com> wrote: > Il 15/02/2017 19:11, Alessandro Baggi ha scritto: > >>

Re: [GENERAL] FATAL: remaining connection slots are reserved for non-replication superuser connections

On Tue, Feb 7, 2017 at 6:52 PM, Patrick B wrote: > Hi guys, > > I get these messages at least once a day in my Prod environment: > >> FATAL: remaining connection slots are reserved for non-replication >> superuser connections > > I do not have a DB pooler and my

Re: [GENERAL] BST Time Zone Discrepancy

On Mon, Feb 6, 2017 at 12:44 PM, Igal @ Lucee.org wrote: > Tom, > > Thank you for your reply: > On 2/6/2017 12:18 PM, Tom Lane wrote: > > This is controlled by the timezone_abbreviations file, which if > > you haven't changed it lists: > > # CONFLICT! BST is not unique > # Other

Re: [GENERAL] psql only works with -h (even localhost)

Adrian asks the correct questions. Lacking the answers to those I'm going to venture a guess that a Unix-domain socket exists but access via Unix-domain sockets is somehow blocked, probably by pg_hba.conf. >From the psql man page: "...Not all of these options are required; there are useful

Re: [GENERAL] COPY to question

On Tue, Jan 17, 2017 at 10:23 AM, Rich Shepard wrote: > Running -9.6.1. I have a database created and owned by me, but cannot > copy > a table to my home directory. Postgres tells me it cannot write to that > directory. The only way to copy tables to files is by doing

Re: [GENERAL] COPY: row is too big

... > Numeric is expensive type - try to use float instead, maybe double. >> > > If I am following the OP correctly the table itself has all the columns > declared as varchar. The data in the CSV file is a mix of text, date and > numeric, presumably cast to text on entry into the table. > But a

Re: [GENERAL] Generating sample data

On Tue, Dec 27, 2016 at 12:01 PM, Steve Crawford < scrawf...@pinpointresearch.com> wrote: > You could start here: > http://www.softwaretestingmagazine.com/tools/open-source-test-data- > generators/ > > I have rolled my own on occasion by just pulling some public lists of most

Re: [GENERAL] Generating sample data

You could start here: http://www.softwaretestingmagazine.com/tools/open-source-test-data-generators/ I have rolled my own on occasion by just pulling some public lists of most common given names and family names and toing a full-join. Same for city, streets, etc. -Steve On Tue, Dec 27, 2016 at

Re: [GENERAL] timestamp without timezone to have timezone

On Sun, Nov 6, 2016 at 7:10 AM, Melvin Davidson wrote: > > > > On Sun, Nov 6, 2016 at 9:11 AM, Benjamin Adams wrote: >> >> I have a server that has a column timestamp without timezone. >> >> Is the time still saved? >> if I select column with

Re: [GENERAL] Hardware recommendations?

After much cogitation I eventually went RAID-less. Why? The only option for hardware RAID was SAS SSDs and given that they are not built on electro-mechanical spinning-rust technology it seemed like the RAID card was just another point of solid-state failure. I combined that with the fact that the

Re: [GENERAL] Way to quickly detect if database tables/columns/etc. were modified?

Not sure if it would work for your use-case but what about just monitoring the PostgreSQL log for DDL statements? You may have to filter out temp tables (as you might in the system catalogs as well) but you could probably also watch for specific tablename patterns in case you only need to

Re: [GENERAL] Installing 9.6 RC on Ubuntu [Solved]

> >> In case it is useful for reference, I beat my head on it a bit more and >> replaced "main" with "9.6": >> deb http://apt.postgresql.org/pub/repos/apt/ xenial-pgdg 9.6 >> > > Yes, that is an FAQ item: > > https://wiki.postgresql.org/wiki/Apt/FAQ > > Yes, but to quote Douglass Adams, "It was

Re: [GENERAL] Installing 9.6 RC on Ubuntu [Solved]

On Tue, Sep 13, 2016 at 11:03 AM, Steve Crawford < scrawf...@pinpointresearch.com> wrote: > I'm trying to install 9.6 RC1 on Ubuntu 16.04 Xenial on my laptop and it > seems broken. > > Installation of 9.6 RC1 on Centos was straightforward by comparison - just > add the 9.6 yu

[GENERAL] Installing 9.6 RC on Ubuntu

I'm trying to install 9.6 RC1 on Ubuntu 16.04 Xenial on my laptop and it seems broken. Installation of 9.6 RC1 on Centos was straightforward by comparison - just add the 9.6 yum package and install. Unfortunately Ubuntu seems second-class by comparison. I already have /etc/apt.repos.d/pgdg.list

Re: [GENERAL] 2.5TB Migration from SATA to SSD disks - PostgreSQL 9.2

> > ... > > You can actually reduce the time more by pre-syncing to the new location. > something like: > > rsync -va /var/lib/pgsql/ /var/lib/pgsql2/ > service postgres stop > rsync -va /var/lib/pgsql/ /var/lib/pgsql2/ > > The second rsync will only copy the deltas from the first, it still has to

Re: [GENERAL] Permissions pg_dump / import

Check out the --no-owner and/or --no-acl flags when performing the dump. These eliminate the statements that set and/or alter ownership of database objects. For use in a test server where the username of the test-server database is different than the username on the production server *and* where

Re: [GENERAL] dumb question

On Thu, Jun 2, 2016 at 10:40 AM, Felipe Santos <felipe...@gmail.com> wrote: > > > 2016-06-02 14:23 GMT-03:00 Steve Crawford <scrawf...@pinpointresearch.com> > : > >> Something like: >> >> select max(id) from yourtable where sts=0 and ref_id is

Re: [GENERAL] dumb question

Something like: select max(id) from yourtable where sts=0 and ref_id is null; That assumes that ref_id is null. It would help to see your table structure and the query you tried that doesn't work. If ref_id is actually a character string then you might need ref_id='' or coalesce(ref_id,'')='' if

Re: [GENERAL] psql color hostname prompt

, Steve On Thu, Apr 28, 2016 at 2:41 AM, Francisco Olarte <fola...@peoplecall.com> wrote: > Hi Steve: > > On Wed, Apr 27, 2016 at 7:09 PM, Steve Crawford > <scrawf...@pinpointresearch.com> wrote: > > The various hacks appear to not deal with the fact that there

Re: [GENERAL] Thoughts on "Love Your Database"

On Wed, May 4, 2016 at 8:04 AM, Steve Crawford < scrawf...@pinpointresearch.com> wrote: > First, you hit them over the head with a copy of "SQL Antipatterns: > Avoiding the Pitfalls of Database Programming". It is a tad out of date and > tends to use PHP and MySQL for t

Re: [GENERAL] Thoughts on "Love Your Database"

Perhaps a bit US centric but PHB = Pointy Haired Boss. See the boss in "Dilbert". Cheers, Steve On Wed, May 4, 2016 at 7:55 AM, Pierre Chevalier Géologue < pierrechevalierg...@free.fr> wrote: > Le 04/05/2016 15:25, John McKown a écrit : > >> On Wed, May 4, 2016 at 8:13 AM, Geoff Winkless

Re: [GENERAL] Thoughts on "Love Your Database"

First, you hit them over the head with a copy of "SQL Antipatterns: Avoiding the Pitfalls of Database Programming". It is a tad out of date and tends to use PHP and MySQL for the main examples but does also address different solutions available in PostgreSQL, Oracle. MS SQL server, etc. while

Re: [GENERAL] psql color hostname prompt

> > 2) %M vs shell call > > %M on when connected to the local machine displays the string "[local]" > which I didn't like. I wanted a real hostname to show no matter which > client/server pair I was using. Zero chance for mistaken commands on the > wrong host. Many times we ssh to a remote

Re: [GENERAL] Export binary data - PostgreSQL 9.2

What exactly are you trying to do? Dump/backup your data (e.g. pg_dump)? Read binary data from a table? If so, what field type (bytea, blob, ...)? Export to where? Cheers, Steve On Sun, Feb 28, 2016 at 9:12 AM, drum.lu...@gmail.com wrote: > Hi all, > > > Which command

Re: [GENERAL] PostgreSQL flavors

Congratulations on the decision and welcome. As an overview, there is the PostgreSQL *project* which is run by the PostgreSQL Global Development Group (PgDG) with contributors around the world most of whom work for a variety of companies that either use or support PostgreSQL. PostgreSQL is

[GENERAL] Transactions, stats and analyze (oh-my)

We have certain processes that import data then process and distribute the data. Since the processing looks primarily, but not exclusively, at the new records an ANALYZE prior to processing yields better plans. Although the table changes will trigger autovacuum to analyze the table this happens

Re: [GENERAL] Postgres and timezones

; but none for "PDT". Come spring, that will change. Cheers, Steve On Wed, Jan 20, 2016 at 7:46 AM, Pavel Stehule <pavel.steh...@gmail.com> wrote: > > > 2016-01-20 16:38 GMT+01:00 Steve Crawford <scrawf...@pinpointresearch.com> > : > >> Is this of

Re: [GENERAL] Postgres and timezones

Is this of any use? select * from pg_timezone_names where name = 'Europe/Lisbon'; name | abbrev | utc_offset | is_dst ---+++ Europe/Lisbon | WET| 00:00:00 | f -Steve On Wed, Jan 20, 2016 at 7:24 AM, Steve Rogerson

Re: [GENERAL] Deletion Challenge

The two general solutions are the "keep the last one" proposed by Adrian "keep the last N" that I sent. But it might be worth stepping back a bit. You said you are having performance problems that you feel would be improved by removing only a million rows which doesn't sound like that much to me.

Re: [GENERAL] Deletion Challenge

If I understand correctly the value of "click" always advances and within a "click" the "cash_journal_id" always advances - not necessarily by single steps so within a fairian_id, ordering by "click" plus "cash_journal_id" would return the records in order from which you want the most recent 5 for

Re: [GENERAL] Packages for Ubuntu Wily (15.10)

You should be able to add the pgdg repository to your system and then install through apt as normal. Scroll down to the "PostgreSQL APT repository" section on this page: http://www.postgresql.org/download/linux/ubuntu/ Cheers, Steve On Mon, Dec 7, 2015 at 9:27 AM, Antony Gelberg

Re: [GENERAL] Pgbouncer

Do you have any clients connected that are idle in transaction? Cheers, Steve On Mon, Nov 30, 2015 at 1:46 PM, Torsten Förtsch wrote: > Hi, > > I am not sure if this is the right place to ask this question. If not, > please point me to it. > > I am trying out the new

Re: [GENERAL] Queuing query

appropriately. I'm really looking for any things like planner ordering nuances that would make the query operate in unexpected ways. Cheers, Steve On Tue, Sep 22, 2015 at 9:26 AM, Jeff Janes <jeff.ja...@gmail.com> wrote: > On Mon, Sep 21, 2015 at 3:51 PM, Steve Crawford < > sc

[GENERAL] Queuing query

While awaiting the awesomeness of the upcoming "skip locked" feature in 9.5 I need to handle a work queue. Does anyone see any glaring issues or subtle nuances with the basic method below which combines CTEs with queue-handling methods posted by depesz, on the PG wiki and elsewhere. Note that it

Re: [GENERAL] Very puzzling sort behavior

Any null values in first name?? -Steve On Thu, Sep 10, 2015 at 12:35 PM, Ken Tanzer wrote: > Hi. In a table that includes these columns: > > my_db=> \d tbl_client > ... > name_last | character varying(40) | not null > name_first

Re: [GENERAL] Public facing PostgreSQL hosting ?

On Mon, Aug 31, 2015 at 12:03 AM, essam Ganadily wrote: > hi > i do develop on PostgreSQL from home and from work. i need public facing > PostgreSQL , something i can use Pgadmin from anywhere. > performance and scalability is not important because i will be running > like

Re: [GENERAL] Problem with database connections timing out for long-running queries

You might check the stunnel settings. A quick search of stunnel 12-hours indicates that this is the stunnel default for idle connections. Cheers, Steve On Fri, Aug 21, 2015 at 11:16 AM, Rich Schaaf rsch...@commoninf.com wrote: I’m running into a problem where the connection between

Re: [GENERAL] regexp_matches for digit

On 07/09/2015 09:24 AM, Ramesh T wrote: Hi, in oracle regexp_like(entered date,'[[:digit:]]{4}-[[:digit:]]{2}-[[:digit:]]{2}','i') for postgres i have regexp_matches ,But i need how to match [:digit:] in postgres when we pass date..? any help Konsole output The tilde operator

[GENERAL] Re: [PERFORM] Query running slow for only one specific id. (Postgres 9.3) version

On 06/05/2015 10:54 AM, Sheena, Prabhjot wrote: Postgresql 9.3 Version Guys Here is the issue that I’m facing for couple of weeks now. I have table (size 7GB) *If I run this query with this specific registration id it is using the wrong execution plan and takes more than a

Re: [PERFORM] Re: [GENERAL] Re: Query running slow for only one specific id. (Postgres 9.3) version

On 06/05/2015 12:28 PM, Steve Crawford wrote: On 06/05/2015 11:38 AM, Sheena, Prabhjot wrote: When I run vacuum analyze it fixes the problem but after 1 or 2 days the problem comes back Is autovacuum running and using what settings? (select name, setting from pg_settings where name

Re: [GENERAL] Re: Query running slow for only one specific id. (Postgres 9.3) version

On 06/05/2015 11:38 AM, Sheena, Prabhjot wrote: When I run vacuum analyze it fixes the problem but after 1 or 2 days the problem comes back Is autovacuum running and using what settings? (select name, setting from pg_settings where name ~ 'autovacuum' Konsole output or name ~

Re: [GENERAL] date with month and year

On 05/21/2015 10:45 AM, Paul Jungwirth wrote: You really shouldn't use WITHOUT TIME ZONE. I'd like to know more about this. Can you say why? Start by reading about the date and time data types with special attention to section 8.5.3:

Re: [GENERAL] date with month and year

On 05/21/2015 10:01 AM, Daniel Torres wrote: I everybody, I'm new in the Postgresql world, and have an easy question: Is it possible to have date type data that only contain month and year?, how can I obtain that from a timestamp (without time zone) column?... Others have offered good tips

[GENERAL] ERROR: could not open relation with OID

This morning we got the following error from a daily script that produces a simple largest-table report: ERROR: could not open relation with OID 597597503 I reran the script and it completed without error. Our server is running 9.1.15 from PgDg Ubuntu repos and the query run by the script is:

Re: [GENERAL] ERROR: could not open relation with OID

On 04/22/2015 01:25 PM, Adrian Klaver wrote: If it is of importance, it appears that a temporary table and temporary index were being created within the same second that the query was run. Any advice? WHERE relkind = 'r' AND relpersistence != 't' So to confirm. Fix the query and

Re: [GENERAL] now() vs 'epoch'::timestamp

On 04/02/2015 10:34 AM, David G. Johnston wrote: On Thu, Apr 2, 2015 at 10:27 AM, James Cloos cl...@jhcloos.com mailto:cl...@jhcloos.comwrote: SC == Steve Crawford scrawf...@pinpointresearch.com mailto:scrawf...@pinpointresearch.com writes: ... What I haven't determined

Re: [GENERAL] now() vs 'epoch'::timestamp

On 04/01/2015 11:50 AM, James Cloos wrote: I've for some time used: (now()::timestamp without time zone - 'epoch'::timestamp without time zone)::reltime::integer to get the current seconds since the epoch. The results are consistant with date +%s. (Incidently, is there a better way in

Re: [GENERAL] Timezone mismatch

On 03/20/2015 08:29 AM, Leonardo M. Ramé wrote: Hi, I had to change the O.S. timezone and aparently PostgreSql continues using the old timezone, how can I force update it's time zone?. Using PostgreSql 8.4 on Ubuntu Server 12.04. To update the OS timezone I used sudo dpkg-reconfigure tzdata

[GENERAL] libs and upgrades

(reposting - should have originally posted here in general - sorry) To prepare for server upgrades I'm planning to update the clients on a set of servers from 9.1 to 9.4. The servers on which the clients are installed are running CentOS 5 i386. Somewhere between PostgreSQL 9.1 and 9.4 the

Re: [GENERAL] can you please share sample Postgres config file to enable max logging with syslog support?

On 12/18/2014 09:00 PM, M Tarkeshwar Rao wrote: Hello friends, can you please share sample Postgres config file to enable max logging with syslog support? Actually we are facing some issues. We need to enable maximum logging. The # ERROR REPORTING AND LOGGING section in postgresql.conf

Re: [GENERAL] Modeling Friendship Relationships

On 11/11/2014 02:38 PM, Robert DiFalco wrote: I have a question about modeling a mutual relationship. It seems basic but I can't decide, maybe it is 6 of one a half dozen of the other. In my system any user might be friends with another user, that means they have a reciprocal friend

Re: [GENERAL] which Update quicker

On 09/23/2014 12:35 PM, Emi Lu wrote: Hello list, For a big table with more than 1,000,000 records, may I know which update is quicker please? (1) update t1 set c1 = a.c1 from a where pk and t1.c1a.c1; .. update t1 set c_N = a.c_N

Re: [GENERAL] PostgreSQL Portable

On 09/10/2014 02:00 PM, Daniel Begin wrote: First, I am a Newbie regarding PostgreSQL … I just started to look at PostgreSQL to implement a large GIS DB (1Tb). The data must reside in an external disk with eSATA connection and may be moved to different locations (and Windows

Re: [GENERAL] Timezone difference between Oracle SYSDATE and PostgreSQL timestamp functions

On 08/28/2014 10:06 PM, Vinayak wrote: Hello, We have converted Oracle SYSDATE to PostgreSQL statement_timestamp() but there is a difference in timezone. SYSDATE returns the time on the server where the database instance is running(returns operating system time) so the time depends on the OS

Re: [GENERAL] NOT IN and NOT EXIST

On 07/03/2014 08:35 AM, Sameer Kumar wrote: Hi, Postgres optimizer automatically tries to convert an IN clause to Hash Join (something similar to EXISTS equivalent of that query). Does a similar translation happen for NOT IN as well? Given that the column used is NOT NUL. Select * from

Re: [GENERAL] How can I replace the year of the created_at column with the current year dynamically ?

On 07/01/2014 11:27 PM, Arup Rakshit wrote: Here is my try : staging::= select to_char(created_at,'DD/MM') || '/' || to_char(now(),'') as when from users; when 24/02/2014 ... 20/02/2014 (15 rows) Can the same be done using any other clever trick ? No tricks are

Re: [GENERAL] Not able to understand how to write group by

On 07/02/2014 09:55 AM, Arup Rakshit wrote: SELECT users.gender,count(*) as participant,avg(daily_action_answers.value) as value FROM users INNER JOIN daily_action_answers ON daily_action_answers.user_id = users.id INNER JOIN measures ON measures.id = daily_action_answers.measure_id WHERE

Re: [GENERAL] Fast data, slow data

On 06/26/2014 02:29 AM, Tim Uckun wrote: I have a use case in which the most recent data experiences a lot of transactions (inserts and updates) and then the churn kind of calms down. Eventually the data is relatively static and will only be updated in special and sporatic events. I was

Re: [GENERAL] How can I get first day date of the previous month ?

On 06/20/2014 12:11 AM, Arup Rakshit wrote: Thanks for your answer. How to get the first day date of last 6 months from now then will be : yelloday_development=# select date_trunc('month', now()) - interval '5 month' as first_month; first_month --- 2014-01-01

Re: [GENERAL] Strange Error in postgresql 8.4

On 06/20/2014 01:18 AM, Dick Kniep wrote: Hi list, ... Now recently I have noticed a strange problem. ... psycopg2.DatabaseError: SSL error: ccs received early Did you try Googling SSL error: ccs received early? Like Adrian, I suspect your answer will be found in one of those messages. What

Re: [GENERAL] Overlapping ranges

On 06/18/2014 04:47 PM, Jason Long wrote: I have a large table of access logs to an application. I want is to find all rows that overlap startdate and enddate with any other rows. The query below seems to work, but does not finish unless I specify a single id. select distinct a1.id from

[GENERAL] unknown type name ‘pg_int64’

I am getting the error following errors trying to install pg_repack via pgxn on Ubuntu server 12.04.4 with all PostgreSQL packages from pgdg: gcc -g -O2 -fstack-protector --param=ssp-buffer-size=4 -Wformat -Wformat-security -Werror=format-security -fPIC -pie -fno-omit-frame-pointer -Wall

Re: [GENERAL] unknown type name ‘pg_int64’

On 06/11/2014 11:35 AM, Tom Lane wrote: Steve Crawford scrawf...@pinpointresearch.com writes: I am getting the error following errors trying to install pg_repack via pgxn on Ubuntu server 12.04.4 with all PostgreSQL packages from pgdg: /usr/include/postgresql/libpq-fe.h:547:1: error: unknown

Re: [GENERAL] How can I select rows by comparing an array data type column with multiple values ?

On 06/02/2014 03:54 AM, Arup Rakshit wrote: Hi, Suppose, I have a table as below :- id |title |content | tags +--++--- 1 | sample post | lorem ipsum| {apple,orange} 2 | another post | fruits are bad | {apple,hamburger}

[GENERAL] Force specific index disuse

Is there a way to force a specific index to be removed from consideration in planning a single query? Specifically, on a 60-million-row table I have an index that is a candidate for removal. I have identified the sets of nightly queries that use the index but before dropping it I would like

Re: [GENERAL] Force specific index disuse

On 05/20/2014 09:44 AM, Seamus Abshere wrote: On 5/20/14, 1:38 PM, Steve Crawford wrote: Is there a way to force a specific index to be removed from consideration in planning a single query? hi Steve, What is the query? Or at least a sanitized but complete version? I've now resolved

Re: [GENERAL] Force specific index disuse

On 05/20/2014 10:44 AM, Alvaro Herrera wrote: Steve Crawford wrote: Is there a way to force a specific index to be removed from consideration in planning a single query? Specifically, on a 60-million-row table I have an index that is a candidate for removal. I have identified the sets

Re: [GENERAL] Force specific index disuse

On 05/20/2014 11:48 AM, Steve Crawford wrote: ... What would happen if you did: BEGIN; DROP INDEX bothersome_idx; INSERT INTO indexed_table...; ROLLBACK; Never mind. Thought it through. Cheers, Steve -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes

Re: [GENERAL] Ubuntu Packages / Config Files

On 05/01/2014 11:40 AM, Stephan Fabel wrote: I'm using Ubuntu 12.04 for these deployments at the moment. The Ubuntu packages don't put the configuration files with the cluster data (by default under /var/lib/postgresql/9.1/main under 12.04), but in /etc/postgresql/9.1/main) and they start

Re: [GENERAL] reindexdb

On 04/29/2014 07:22 AM, Steve Clark wrote: Hello, We have a small (2GB) 8.4.20 database. The prior maintainer ran a reindexdb twice a day (originally started with 7.3.x). Can anybody see a reason why we need to continue to do this? I don't. It certainly appears to be a piece of duct-tape

Re: [GENERAL] timezone datetime issue

On 04/16/2014 03:56 AM, Glenn Pierce wrote: Hi I have an issue with adjusting a timestamp. I have a table like CREATE TABLE sensor_values ( ts timestamp with time zone NOT NULL, value double precision NOT NULL DEFAULT 'NaN'::real, ) It was intended that ts timestamps would be the

Re: [GENERAL] openssl heartbleed

On 04/10/2014 01:01 AM, Albe Laurenz wrote: Steve Crawford wrote: If you aren't and weren't running a vulnerable version or if the vulnerable systems were entirely within a trusted network space with no direct external access then you are probably at low to no risk and need to evaluate

Re: [GENERAL] openssl heartbleed

On 04/09/2014 08:54 AM, Gabriel E. Sánchez Martínez wrote: Hi all, Our server is running Ubuntu Server 13.10 (we will soon upgrade to 14.04) and PostgreSQL 9.1. We use certificates for all client authentication on remote connections. The server certificate is self-signed. In light of the

Re: [GENERAL] Log file monitoring and event notification

On 04/05/2014 08:47 AM, Andy Colson wrote: Hi All. I've started using replication, and I'd like to monitor my logs for any errors or problems. I don't want to do it manually, and I'm not interested in stats (a la PgBadger). What I'd like, is the instant PG logs: FATAL: wal segment already

Re: [GENERAL] SSD Drives

On 04/03/2014 12:44 PM, Brent Wood wrote: Hi David, Does the RAID 1 array give any performance benefits over a single drive? I'd guess that writes may be slower, reads may be faster (if balanced) but data security is improved. I've been looking into upgrading to SSD and wondering about RAID

Re: [GENERAL] SSD Drives

On 04/04/2014 10:15 AM, Merlin Moncure wrote: 2. Do I need both BBU on the RAID *and* capacitor on the SSD or just on one? Which one? I'm suspecting capacitor on the SSD and write-through on the RAID. You need both. The capacitor protects the drive, the BBU protects the raid controller. ?? In

Re: [GENERAL] Mysterious DB reset

On 03/06/2014 09:33 AM, Israel Brewster wrote: For starters, this happened again this morning (no data prior to 4:45 am and sequence reset), so whatever is going on appears to be reoccurring. Also, I forgot to mention if it is significant: this is running on slackware liunux 14.0 Also odd is

Re: [GENERAL] Cancelling of autovacuums considered harmful

On 02/26/2014 08:56 AM, Alvaro Herrera wrote: ... No matter how heavily updated, regular activity should not cause autovacuum kills. Only heavier operations would do that (say ALTER TABLE, etc). Considered harmful got my attention. What, if any, known harm is caused? We have many errors of

Re: [GENERAL] Cancelling of autovacuums considered harmful

On 02/26/2014 04:40 PM, Steve Crawford wrote: On 02/26/2014 08:56 AM, Alvaro Herrera wrote: ... No matter how heavily updated, regular activity should not cause autovacuum kills. Only heavier operations would do that (say ALTER TABLE, etc). Considered harmful got my attention. What, if any

Re: [GENERAL] password-less access, without using pg_hba

On 02/06/2014 06:07 PM, Reece Hart wrote: I'd like to provide public access, without a password, to a database hosted on Amazon RDS. I'm familiar with using pg_hba.conf to enable trust (no) authentication for a user. pg_hba.conf is not available to DBAs on RDS. Is there any other way to

Re: [GENERAL] subtracting from a date

On 02/06/2014 09:25 AM, Jay Vee wrote: I have reviewed working with dates documentation but some things are not clear and I cannot get an example to work for what I need. I am passing a date into a stored function like '2013-04-01' The stored function accepts this string as a date type.

Re: [GENERAL] A Simple web application

On 01/27/2014 07:19 AM, Raymond O'Donnell wrote: On 27/01/2014 15:16, Edson Richter wrote: Em 27/01/2014 10:42, Andreas Kretschmer escreveu: Raymond O'Donnell r...@iol.ie wrote: On 27/01/2014 12:11, Manoj Soni wrote: Name, Age and Sex Which tutorial, may help me in this regard. I did a

[GENERAL] postgresql-common breaks logrotate

People who have attempted to install PGDG versions of PostgreSQL on recent Ubuntu releases have run into the cascading problem of postgresql wanting to destroy Ubuntu. Based on the packaging: postgresql depends on postgresql-common postgresql-common breaks logrotate (3.8) ubuntu-standard

[GENERAL] Does --no-unlogged-table-data prevent table locking?

If a database has an unlogged table and that table is truncated during while a database dump with the --no-unlogged-table-data option is running, will the table be locked against a truncate or will the truncate succeed? The documentation is unclear on this detail. Background: We have a

Re: [GENERAL] Junk date getting uploaded into date field

On 11/05/2013 05:29 AM, Albe Laurenz wrote: Thomas Kellerer wrote: bsreejithin, 05.11.2013 13:14: Not able to post the attached details as a comment in the reply box, so attaching it as an image file : http://postgresql.1045698.n5.nabble.com/file/n5776987/Untitled.png It would have much

Re: [GENERAL] Table partitioning

On 10/28/2013 09:27 AM, Herouth Maoz wrote: I have a rather large and slow table in Postgresql 9.1. I'm thinking of partitioning it by months, but I don't like the idea of creating and dropping tables all the time. What is slow about it? Inserting? Selecting? Deleting? Partitioning can assist

Re: [GENERAL] pg_dumpall from a script

On 10/21/2013 10:20 PM, James Sewell wrote: That looks great, but it doesn't really help with my problem unless I'm missing something (very possible!) I need a way to backup either from SQL in PSQL (possibly \!) or from a PG/PLSQL function to a file with a name set from a :variable. This

Re: [GENERAL] Forms for entering data into postgresql

On 10/12/2013 01:57 PM, Adrian Klaver wrote: ... In the text based systems I am familiar with their where keyboard shortcuts that took you directly to fields and coding conventions that allowed direct entry of data. For example at a plumbing supply house I went to the convention was something

Re: [GENERAL] Need some help on Performance 9.0.4

On 10/11/2013 08:56 AM, akp geek wrote: We have been running 4 of our applications on 9.0.4, which we are planning to update the database 9.2.2 by the year end Sorry if I missed it but I couldn't find a question or description of a problem you are encountering. Cheers, Steve -- Sent

Re: [GENERAL] ERROR: invalid value ???? for YYYY

On 10/09/2013 05:57 PM, Brian Wong wrote: But from a user's perspective, why would it ever make sense that by adding an additional where clause, it actually brings in more data into the picture? If I have query returning 100 rows. Adding an additional where clause should only cut down the

  1   2   3   4   5   6   >