Tag Info

New answers tagged

4

The size of the physical table is typically (except for opportunistic pruning of removable pages from the end of the table) not reduced by running VACUUM (or VACUUM ANALYZE). You need to run VACUUM FULL to actually shrink the table. That's not necessarily what you want to do on a regular basis if you have write load on your table. Dead rows provide wiggle ...


1

Disabling thread safety will mean that libpq can't be safely used with multi-threaded applications unless the app is very careful to only interact with it using a single thread. This isn't a concern for the great majority of apps.


1

It turns out that I was putting my pidfile in /var/run which is fine for running it but when server is rebooted the file is deleted and pgbouncer can't find the file and it gives the this error FATAL @src/main.c:553 in function write_pidfile(): /var/run/pgbouncer/pgbouncer.pid: No such file or directory So simply moving the file to another location and ...


1

The composite type is clean design, but it does not help performance at all. First of all, float translates to float8 a.k.a. double precision in Postgres. You are building on a misunderstanding. The real data type occupies 4 byte (not 8). It has to be aligned at multiples of 4 bytes. Measure actual sizes with pg_column_size(). SQL Fiddle demonstrating ...


1

The parameter INTERNALLENGTH is only applicable to the creation of a new base type, which is a rather specialized operation for advanced users. It would require to provide input and output function etc. What you display is the creation of a new composite type, which is a more common operation. There is no parameter INTERNALLENGTH for that purpose. Read the ...


0

Obviously, you need to configure a different port to listen to, since 6432 is already in use. Are you starting two instances of pgbouncer? Or some other program is already listening on port 6432? Find out which: Finding the process that is using a certain port in Linux Sort that out or set a different port in your config.ini (random example port number): ...


0

Looking at the linked code you don't understand: select count(*) from ( select * From EmpDtl1 union select * From EmpDtl2 ) The secret sauce is using union as opposed to union all. The former retains only distinct rows whereas the latter keeps duplicates (reference). In other words the nested querys says "give me all rows and columns from EmpDtl1 and in ...


2

You will need to replicate the table from the read/write master. PostgreSQL's built-in physical streaming replication can only replicate the whole database server instance. The replication systems that support single-table replication require the ability to use a trigger to write a record of changes. This can only be done on the upstream read/write master. ...


0

SELECT * FROM stickers WHERE keywords @@ to_tsquery('case & 580:*') will work. Postgres text search allows for prefix searching, which is represented by the :* in the query. It will match any token starting with 580


0

No you don't need separate indexes. Use the weights feature. They are just a label your can query against. You can have up to four labels to query against (A-D). --search any "field" for quick: select 'quick:1A brown:2B quick:3C'::tsvector @@ 'quick'::tsquery; --true --search B "field" for quick: select 'quick:1A brown:2B quick:3C'::tsvector @@ ...


1

VACUUM ANALYZE makes the difference in your example. Plus, as @jjanes supplied, the additional statistics for the functional index. Per documentation: pg_statistic also stores statistical data about the values of index expressions. These are described as if they were actual data columns; in particular, starelid references the index. No entry is made ...


4

When you create an expression index, it causes PostgreSQL to gather statistics on the that expression. With those statistics on hand, it now has an accurate estimate for the number of aggregated rows that the query will return, which leads it to make a better plan choice. Specifically in this case, without those extra statistics it thought the hash table ...


5

One option is to use a FULL OUTER JOIN between the two tables in the following form: SELECT count (1) FROM table_a a FULL OUTER JOIN table_b b USING (<list of columns to compare>) WHERE a.id IS NULL OR b.id IS NULL ; For example: CREATE TABLE a (id int, val text); INSERT INTO a VALUES (1, 'foo'), (2, 'bar'); CREATE ...


6

You can use the EXCEPT operator. For example, if the tables have identical structure, the following will return all rows that are in one table but not the other (so 0 rows if the tables have identical data): (TABLE a EXCEPT TABLE b) UNION ALL (TABLE b EXCEPT TABLE a) ; Or with EXISTS to return just a boolean value or a string with one of the 2 possible ...


3

In Debian/Ubuntu, the functionalities of pg_ctl are provided by pg_ctlcluster. Quoted from its manpage: NAME pg_ctlcluster - start/stop/restart/reload a PostgreSQL cluster SYNOPSIS pg_ctlcluster [options] cluster-version cluster-name action -- [pg_ctl options] where action = start|stop|restart|reload|promote DESCRIPTION ...


1

This is some kind of misunderstanding. The query in your question already returns what you are asking for. I only changed minor details: SELECT 'Inspections'::text AS data_label ,count(i.reporting_id) AS daily_count ,d.day AS date_column FROM ( SELECT day::date FROM generate_series(date '2013-01-01' , date ...


0

You are mixing requirements for representation and storage. That's a common misconception for people coming from spreadsheet programs, where you typically do both at once. Just store a serial column. The underlying integer column occupies 4 bytes and is very efficient for various purposes in the DB. In comparison, 'PAT0000001' as text or varchar occupies 11 ...


1

This sort of question (have new project, how do I...) appears frequently here. I will urge you to do what I would urge everybody who has a new project to do - stand on the shoulders of giants. Check out any/a few/all Open Source projects that do what you are interested in doing (or similar) and learn from their database schemas and adapt what has already ...


0

For a data warehousing/analytics type application (DW/OLAP), I would go with PostgreSQL. It has set operators, windowing functions (also known as analytic functions) and common table expressions. You will most likely have to implement some or all of these in your own code with MySQL with the attendant possibility of bugs. MySQL is more suited to read-heavy ...


0

1) Any modern RDBMS system will allow you to import and work with Excel-like files (usually, we use them as CSV files, for which both databases have import support: COPY and LOAD DATA). If you had to choose, worry about in-house technology knowledge and advanced features, but nothing here suggest any impossible tasks. Please be wary of people promoting ...


1

PostgreSQL does not have "auto-increment" fields in the sense of MySQL's AUTO_INCREMENT, but I'm guessing you mean SERIAL. If so, yes, what you describe is possible, but please, please don't do this. A SERIAL is just shorthand for a CREATE SEQUENCE and a default value. e.g. CREATE TABLE blah( id serial primary key ); is actually shorthand for: ...


3

Would a different kind of column be faster? For example an integer No. timestamp and timestamptz are just unsigned 64-bit integers internally anyway. Is there some way to not lock the column? It doesn't lock the column. It takes weak table lock that doesn't really block anything except DDL, and takes a row level lock on the row you're updating. ...


0

If you have not tried this already, review your pg_hba.conf file. It will be named something like /var/lib/pgsql/9.3/data/pg_hba.conf (Fedora 20); you may have to use 'find / -name pg_hba.conf' to locate it. At the bottom of the file, change the 'METHOD' values to 'trust' for local testing (see postgres docs for full information). Reboot the machine to ...


0

Thanks to @PieterGeerkins I've started to take a look into double-entry bookkeeping more, which seems to have helped me resolve the original problem. Using one 'account_id' in 'chart_of_accounts' for the jackpot (instead of one 'account_id' for each jackpot time period) allows me to not worry about referencing 'account_id' from the table 'jackpots'. I'll ...


0

with days as (select day::date from generate_series(date '2013-01-01', date '2013-01-01' + 3, interval '1 day' day) day ), inspection as (select date '2013-01-01' AS close_case_date UNION ALL select date '2013-01-03') select (SELECT COUNT(*) FROM inspection AS i WHERE i.close_case_date = d.day) AS daily_count, d.day as date_column from days d ...


1

I ran into the same problem. To clarify what Nyxynyx hints towards in his comment, the resolution was to give permissions to access the db cluster dir (the path that follows -D in the error): chown -R postgres:postgres /var/lib/postgresql/9.1/main


2

Assuming referential integrity and all columns to be NOT NULL, this should be simplest and fastest: SELECT * FROM Receipe r WHERE NOT EXISTS ( SELECT 1 FROM Ingredients i JOIN RecipeIngredients ON ri.IngredientsId = i.ID WHERE ri.RecipeID = r.Id AND ri.RequiredQuantity > i.AvailableQuantity ); Basically, use a NOT EXISTS ...


1

As @dezso suggested, the table with test data was not big enough for the query planner bother going to the index. After I imported a larger set of data, the query uses the index as expected.


1

Using psql variables with quotes is a bit tricky. As you noticed, they don't get substituted in certain positions, among other, between quotes. My only solution to this so far has been including the quote in the variable itself. This is also not very straightforward: test=# \set a 'parrot' test=# \echo :a parrot test=# \set a ''parrot'' test=# \echo :a ...


1

If I run them serially, one right after the other, I'm expecting it will require 7 minutes to complete on average. Is this reasonable? If they use unrelated data sets, then yes. If they share a data set, and the cache is cold for the first query and the query is mostly I/O bound, then the second one might complete in moments. You need to consider ...


1

Start with: Create a ChartOfAccounts table with the Account code as Primary Key. Add a Foreign Key constraint to ChartOfAccounts on all tables with an AccountCode field. Use an IsDebit field, not the numeric sign, to distinguish Debits from Credits and reserve negative signs for transction reversals (if used at all). This is necessary in order to generate ...


0

Here is one way to do: select id,name from recipe; 1 Recipe 1 2 Recipe 2 3 Recipe 3 select recipe_id,ingredient_id,required_quantity from recipe_ingredients; 1 1 10 1 2 5 1 4 10 2 1 10 2 2 5 2 3 20 select id,name,available_quantity from ingredients; 1 Ingredient 1 100 2 Ingredient 2 10 3 Ingredient 3 30 4 ...


1

Do the exclusion in the cron job definition. The handy flock shell command is intended for exactly this. To do it inside PostgreSQL you would have the function take a lock that it holds for the duration of its run. Lots of options - LOCK TABLE ... IN EXCLUSIVE MODE, do a SELECT ... FOR UPDATE of a particular row, or use pg_advisory_xact_lock. See the ...


0

It turns out that I have made a mistake in explaining the problem. The SORT operation is not the most expensive part in this query, as shown in the second figure. The most expensive part is the second nested loop, which is responsible to materialise and join the activity table. The database engine need to fetch all the columns from the activity table. The ...


1

select c.id, (select row_to_json(_) from (select c.first_name, c.last_name) as _) as first_last, c.age from customers as c will do what you want without any performance impact (and is not too verbose): id | first_last | age ------+---------------------------------------------+--------- 1 | ...


4

ORDER BY is not that expensive by itself. The problem here is that it is used together with OFFSET. So postgresql does sorting of all data before it can take needed 10 rows. You are probably implementing paging using ORDER BY + OFFSET. This is a known postgresql limitation and there is postgresql way to handle this.


3

As my boss stated, all I had to do was edit the inner statements instead of trying to manipulate the results on the outer query. We managed to do what I needed with this: SELECT y.datamov, y.n_conta, y.sinal, y.valor, a.nome, '', 0, y.lrdarc, y.quantidade FROM ( SELECT k.datamov, k.n_conta, k.sinal, ...


0

You can just use a CASE expression, something like: SELECT y.datamov, y.n_conta, y.sinal, y.valor, y.qtde, a.nome, '', 0, a.lrdarc, CASE WHEN MOD(a.lrdarc,5)=1 THEN y.FirstMergeColumn || y.SecondMergeColumn ELSE a.lrdarc END ... Without knowing more, hard to guess what you ...


0

I believe you want to show all categories. If so, you will need to make all of your joins RIGHT OUTER JOINS. Why? Associativity and precedence for outer join operators is poorly defined and unpredictable You need to keep going 'outer' once you've started. Because you're using a RIGHT join, each preceding join must also be a RIGHT join. If not, the NULLs ...


3

It sounds like you are looking for a FULL [OUTER] JOIN. Per documentation: FULL OUTER JOIN First, an inner join is performed. Then, for each row in T1 that does not satisfy the join condition with any row in T2, a joined row is added with null values in columns of T2. Also, for each row of T2 that does not satisfy the join condition with any row ...


3

By creating a unique constraint on your table, you are telling the database "each time I try to insert a row in this table, please check that there is no existing row with this combination of columns the same. Oh, and make sure you do it in an atomic fashion so that if someone else tries to insert one at the same time as me, only one of us will succeed". ...


3

You seem to expect that rows with NULL values are excluded from a B-tree index automatically, but that's not the case. Those are indexed as well and can be searched for. However, since: access_type ... is null in 90% of cases that's hardly useful in your case. Such common values hardly ever make sense in an index to begin with, be it NULL or any other ...


1

Pattern matching and operators Full text search is not the right tool for pattern matching (and possibly even fuzzy, fault tolerant input). Typically, trigram-similarity search with the % operator is the superior approach here. You need to install the additional module pg_trgm once per database: How do I create an index to speed up an aggregate LIKE query ...


2

This is a parameter that is processed by the OpenSSL library. The format and options are documented in the ciphers(5) man page. For the ones you mention: ALL all cipher suites except the eNULL ciphers which must be explicitly enabled; as of OpenSSL, the ALL cipher suites are reasonably ordered by default ADH anonymous DH cipher suites, ...


1

In Feb 2009, Scahin_S wrote in [1] to use the uninstall-postgresql binary. In the same thread Sachin_S also documented a manual alternative for uninstalling 8.3 is /opt/PostgreSQL/8.3/installer/server/removeshortcuts.sh /opt/PostgreSQL/8.3 8.3 /etc/init.d postgresql-8.3 stop rm -rf /opt/PostgreSQL rm /etc/postgres-reg.ini rm -rf ...


0

From what I understand of your problem your "duplicity" is fine. If your saying that all of the shops act totally independently then yes in your example shop E and shop D might have the same data but the data is not related so it might look like a duplicate, but I would not consider that a duplicate. Another example, I am answering this question now, but ...


3

The standard streaming replication on PostgreSQL is single threaded and there is no way to change this. However the question is why would you want to? PostgreSQL's streaming replication works through the write ahead log which is kinda of like a set of instructions "change block 3525 to this", "change block 2424 to this", etc. This makes the replication ...


0

If pg_clog is gone..... thats bad, this stores the commit log so the database knows what has and has not been committed. I would start looking at backups now, or if the data is disposable and there is no backup then building a new cluster. There are ways you can get the data out, but as far as I know they are very time consuming. The bigger question ...


0

To be truly highly available you will need some logic built into the application to handle this. You can do this sort of thing with pg_pool, but what if pg_pool breaks? I think there are many ways you could work this one, but the way I would do it would be: Master DB (lets call it A), and two slaves (lets call them B and C). B pulls changes from A and C ...


0

From looking at your query, I think the planner is making the right choice, you have no WHERE clause or LIMIT, so the database has to return every row anyway, so it has the choice look at the entire table and look at the indexes or look at the entire table. Have you tried restricting the rows with a WHERE clause or used a limit? I cant think of many time ...



Top 50 recent answers are included