Improving Postgres performance on graph-like queries of multi-level self-joins (comparison to Neo4j)

One of the claims Neo4j makes in their marketing is that relational databases aren't good at doing multi-level self-join queries:

I found the code repository corresponding to the book that claim is taken from, and translated it to Postgres:

CREATE TABLE t_user (   id bigserial PRIMARY KEY,   name text NOT NULL );  CREATE TABLE t_user_friend (   id bigserial PRIMARY KEY,   user_1 bigint NOT NULL REFERENCES t_user,   user_2 bigint NOT NULL REFERENCES t_user );  CREATE INDEX idx_user_friend_user_1 ON t_user_friend (user_1); CREATE INDEX idx_user_friend_user_2 ON t_user_friend (user_2);  /* Create 1M users, each getting a random 10-character name */ INSERT INTO t_user (id, name)   SELECT x.id, substr(md5(random()::text), 0, 10)   FROM generate_series(1,1000000) AS x(id);  /* For each user, create 50 random friendships for a total of 50M friendship records */ INSERT INTO t_user_friend (user_1, user_2)   SELECT g1.x AS user_1, (1 + (random() * 999999)) :: int AS user_2   FROM generate_series(1, 1000000) as g1(x), generate_series(1, 50) as g2(y); 

And these are the queries at various depths Neo4j is comparing against:

/* Depth 2 */  SELECT   COUNT(DISTINCT f2.user_2) AS cnt  FROM   t_user_friend f1    INNER JOIN     t_user_friend f2      ON f1.user_2 = f2.user_1  WHERE   f1.user_1 = 1;  /* Depth 3 */  SELECT   COUNT(DISTINCT f3.user_2) AS cnt  FROM   t_user_friend f1    INNER JOIN     t_user_friend f2      ON f1.user_2 = f2.user_1    INNER JOIN     t_user_friend f3      ON f2.user_2 = f3.user_1  WHERE   f1.user_1 = 1;  /* Depth 4 */  SELECT   COUNT(DISTINCT f4.user_2) AS cnt  FROM   t_user_friend f1    INNER JOIN     t_user_friend f2      ON f1.user_2 = f2.user_1    INNER JOIN     t_user_friend f3      ON f2.user_2 = f3.user_1    INNER JOIN     t_user_friend f4      ON f3.user_2 = f4.user_1  WHERE   f1.user_1 = 1;  /* Depth 5 */  SELECT   COUNT(DISTINCT f5.user_2) AS cnt  FROM   t_user_friend f1    INNER JOIN     t_user_friend f2      ON f1.user_2 = f2.user_1    INNER JOIN     t_user_friend f3      ON f2.user_2 = f3.user_1    INNER JOIN     t_user_friend f4      ON f3.user_2 = f4.user_1    INNER JOIN     t_user_friend f5      ON f4.user_2 = f5.user_1  WHERE   f1.user_1 = 1; 

I was roughly able to reproduce the book's claimed results, getting these sorts of execution times against the 1M users, 50M friendships:

| Depth | Count(*) | Time (s) | |-------|----------|----------| | 2     | 2497     | 0.067    | | 3     | 117301   | 0.118    | | 4     | 997246   | 8.409    | | 5     | 999999   | 214.56   | 

(Here's an EXPLAIN ANALYZE of a depth 5 query)

My question is, is there a way to improve the performance of these queries to meet or exceed Neo4j's execution time of ~2s at depth level 5?

I tried with this recursive CTE:

WITH RECURSIVE chain(user_2, depth) AS (   SELECT t.user_2, 1 as depth   FROM t_user_friend t   WHERE t.user_1 = 1 UNION   SELECT t.user_2, c.depth + 1 as depth   FROM t_user_friend t, chain c   WHERE t.user_1 = c.user_2   AND depth < 4 ) SELECT COUNT(*) FROM (SELECT DISTINCT user_2 FROM chain) AS temp; 

However it's still pretty slow, with a depth 4 taking 5s and a depth 5 taking 48s (EXPLAIN ANALYZE)

1 Answers

Answers 1

I'd like to note from the start that comparing relational and non-relation databases are not like-for-like comparison.

It is likely that non-relation database maintains some extra pre-calculated structures as the data is updated. This makes updates somewhat slower and requires more disk space. Pure relational schema that you use don't have anything extra, which makes updates as fast as possible and keeps disk usage to the minimum.

I'll focus on what could be done with the given schema.

---

At first I'd make a composite index

CREATE INDEX idx_user_friend_user_12 ON t_user_friend (user_1, user_2); 

One such index should be enough.

Then, we know that there are only 1M users in total, so final result can't be more than 1M.

The 5-level query ends up generating 312.5M rows (50*50*50*50*50). This is way more than maximum possible result, which means that there are a lot of duplicates.

So, I'd try to materialize intermediate results and eliminate duplicates early in the process.

We know that Postgres materializes CTEs, so I'd try to use that.

Something like this:

WITH CTE12 AS (     SELECT         DISTINCT f2.user_2     FROM         t_user_friend f1          INNER JOIN t_user_friend f2 ON f1.user_2 = f2.user_1     WHERE         f1.user_1 = 1 ) ,CTE3 AS (     SELECT         DISTINCT f3.user_2     FROM         CTE12         INNER JOIN t_user_friend f3 ON CTE12.user_2 = f3.user_1 ) ,CTE4 AS (     SELECT         DISTINCT f4.user_2     FROM         CTE3         INNER JOIN t_user_friend f4 ON CTE3.user_2 = f4.user_1 ) SELECT     COUNT(DISTINCT f5.user_2) AS cnt FROM     CTE4     INNER JOIN t_user_friend f5 ON CTE4.user_2 = f5.user_1 ; 

Most likely SELECT DISTINCT would require sorts, which would allow to use merge joins.

---

As far as I could understand from the execution plan for the query above https://explain.depesz.com/s/Sjov , Postgres is not smart enough and does some unnecessary sorts. Also, it uses hash aggregate for some SELECT DISTINCT, which requires extra sort.

So, the next attempt would be to use temporary tables with proper indexes for each step explicitly.

Also, I'd define the idx_user_friend_user_12 index as unique. It may provide an extra hint to optimizer.

It would be interesting to see how the following performs.

CREATE TABLE temp12 (     user_2 bigint NOT NULL PRIMARY KEY ); CREATE TABLE temp3 (     user_2 bigint NOT NULL PRIMARY KEY ); CREATE TABLE temp4 (     user_2 bigint NOT NULL PRIMARY KEY );  INSERT INTO temp12(user_2) SELECT     DISTINCT f2.user_2 FROM     t_user_friend f1      INNER JOIN t_user_friend f2 ON f1.user_2 = f2.user_1 WHERE     f1.user_1 = 1 ;  INSERT INTO temp3(user_2) SELECT     DISTINCT f3.user_2 FROM     temp12     INNER JOIN t_user_friend f3 ON temp12.user_2 = f3.user_1 ;  INSERT INTO temp4(user_2) SELECT     DISTINCT f4.user_2 FROM     temp3     INNER JOIN t_user_friend f4 ON temp3.user_2 = f4.user_1 ;  SELECT     COUNT(DISTINCT f5.user_2) AS cnt FROM     temp4     INNER JOIN t_user_friend f5 ON temp4.user_2 = f5.user_1 ;  DROP TABLE temp12; DROP TABLE temp3; DROP TABLE temp4; 

As an added bonus of explicit temp tables you can measure how much time each extra level takes.

Read More

Double inserting records in flask SqlAlchemy connected with PostgreSql?

Very rarely, I meet a problem that the record that I inserted into Table Tbl_CUSTOMER was double with auto ID from Postgres.

I have no idea, but I suspected that it could be caused from postgres vacuum running time. To confirm that, I tried to run postgres vacuum at the same with inserting record, but could not found this problem happened, therefore, I could not duplicate the issue to find what was the root cause and fix the problem.

models.py

class Tbl_CUSTOMER():     ID              =   db.Column(db.Numeric(25, 9), primary_key=True, autoincrement=True)     PotentialCustomer   =   db.Column(db.String(12))     FirstNameEn     =   db.Column(db.String(35))     LastNameEn      =   db.Column(db.String(35))     FirstNameKh     =   db.Column(db.String(35))     LastNameKh      =   db.Column(db.String(35))     Salutation      =   db.Column(db.String(4))     Gender          =   db.Column(db.String(6))     DateOfBirth     =   db.Column(db.String(10))     CountryOfBirth  =   db.Column(db.String(2))     Nationality     =   db.Column(db.String(2))     ProvinceOfBirth =   db.Column(db.String(3)) 

views.py

dataInsert =Tbl_CUSTOMER(                 PotentialCustomer   =   request.form['PotentialCustomer'],                 FirstNameEn     =   request.form['FirstNameEn'],                 LastNameEn      =   request.form['LastNameEn'],                 FirstNameKh     =   request.form['FirstNameKh'],                 LastNameKh      =   request.form['LastNameKh'],                 Salutation      =   request.form['Salutation'],                 Gender          =   request.form['Gender'],                 DateOfBirth     =   request.form['DateOfBirth'],                 CountryOfBirth  =   request.form['CountryOfBirth'],                 Nationality     =   request.form['Nationality'],                 ProvinceOfBirth =   request.form['ProvinceOfBirth']             )  db.session.add(dataInsert) db.session.commit() 

This problem does not happen frequently. So, what is the problem, and how can I fix this to prevent it happen in future? Thanks.

1 Answers

Answers 1

If you create a unique key ( or replace your primary key ) with some hashing function value based on all the values of your row, that may help you to see when this problem is happening. Using this hashing column you will be able to decide what you should happen when your system get the same value ( same hash ). One option, for example, just ignores the new row, keeping the old one. Other, is to rewrite, etc.

The chance of getting the same hash value from different rows is so small that I would not even consider that. Look this thread https://crypto.stackexchange.com/questions/1170/best-way-to-reduce-chance-of-hash-collisions-multiple-hashes-or-larger-hash if you want to see more details about that.

Read More

Generate dynamic schedule for Rails application

I need to implement scheduling task for my application. Let's say application display popup questions fetching the schedule data from database.

Heres the database table structure -

Now i want to display question's to logged in user from QuestionSchedule table. Heres the scenario - Question1 should displays X repeatable_times after each X repeat_after_days. Example - Question1 should displays 3 repeatable_times after each 2 repeat_after_days.

Note - UserQuestionAnswer should not display duplicate entry with calculate the UserQuestionAnswer and QuestionSchedule table.

Details data - Question (id-1, title- What is your level of confidence for todays task ?) QuestionSchedule(id-1,question_id-1,repeatable_times-3,repeat_after_days-2) UserQuestionAnswer(id-1,question_id-1,user_id-1,answer_at-(2018-08-27))

Now i want to generate schedule on fly -

2018-08-25 -> Schedule is created

2018-08-26 -> Should not display

2018-08-27 -> Should display and add answer to UserQuestionAnswer table not twice

2018-08-28 -> Should not display

2018-08-29 -> Should display and add answer to UserQuestionAnswer table not twice

2018-08-30 -> Should not display

2018-08-31 -> Should display and add answer to UserQuestionAnswer table not twice

0 Answers

Read More

Docker, Flask, SQLAlchemy: ValueError: invalid literal for int() with base 10: 'None'

I have a flask app that can be initialized successfully and connects to Postgresql database. However, when i try to dockerize this app, i get the below error message. "SQLALCHEMY_DATABASE_URI" is correct and i can connect to it, so i can't figure where I have gone wrong.

docker-compose logs

app_1       |   File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/url.py", line 60, in __init__ app_1       |     self.port = int(port) app_1       | ValueError: invalid literal for int() with base 10: 'None' 

Postgres database connects successfully in Docker container

postgres_1  | LOG:  database system is ready to accept connections 

config.py

from os import environ import os  RDS_USERNAME = environ.get('RDS_USERNAME') RDS_PASSWORD = environ.get('RDS_PASSWORD') RDS_HOSTNAME = environ.get('RDS_HOSTNAME') RDS_PORT = environ.get('RDS_PORT') RDS_DB_NAME = environ.get('RDS_DB_NAME')  SQLALCHEMY_DATABASE_URI = "postgresql+psycopg2://{username}:{password}@{hostname}:{port}/{dbname}"\                           .format(username = RDS_USERNAME, password = RDS_PASSWORD, \                            hostname = RDS_HOSTNAME, port = RDS_PORT, dbname = RDS_DB_NAME) 

flask_app.py (entry point)

def create_app():     app = Flask(__name__, static_folder="./static", template_folder="./static")     app.config.from_pyfile('./app/config.py', silent=True)      register_blueprint(app)     register_extension(app)      with app.app_context():         print(db) -> This prints the correct path for SQLALCHEMY_DATABASE_URI         db.create_all()         db.session.commit()     return app  def register_blueprint(app):     app.register_blueprint(view_blueprint)     app.register_blueprint(race_blueprint)   def register_extension(app):     db.init_app(app)     migrate.init_app(app)   app = create_app()  if __name__ == '__main__':     app.run(host='0.0.0.0', port=8080, debug=True) 

Dockerfile

FROM ubuntu  RUN apt-get update && apt-get -y upgrade  RUN apt-get install -y python-pip && pip install --upgrade pip  RUN mkdir /home/ubuntu  WORKDIR /home/ubuntu/celery-scheduler  ADD requirements.txt /home/ubuntu/celery-scheduler/  RUN pip install -r requirements.txt  COPY . /home/ubuntu/celery-scheduler  EXPOSE 5000  CMD ["python", "flask_app.py", "--host", "0.0.0.0"] 

docker-compose.yml

version: '2'   services:   app:     restart: always     build:        context: .       dockerfile: Dockerfile     volumes:       - .:/app     depends_on:       - postgres    postgres:     restart: always       image: postgres:9.6     environment:       - POSTGRES_USER=${RDS_USERNAME}       - POSTGRES_PASSWORD=${RDS_PASSWORD}       - POSTGRES_HOSTNAME=${RDS_HOSTNAME}       - POSTGRES_DB=${RDS_DB_NAME}     ports:       - "5432:5432" 

1 Answers

Answers 1

You need to set environment variables RDS_USERNAME, RDS_PASSWORD, RDS_HOSTNAME, RDS_PORT , and RDS_DB_NAME in Dockerfile with ENV key value, for example

ENV RDS_PORT 5432 

Read More

Log Stacktrace of Python in PostgreSQL trigger

I am trying to find a bug which happens from time to time on our production server, but could not be reproduced otherwise: some value in the DB gets changed in a way which I don't want it to.

I could write a PostgreSQL trigger which fires if this bug happens, and raise an exception from said trigger. I would see the Python traceback which executes the unwanted SQL statement.

But in this case I don't want to stop the processing of the request.

Is there a way to log the Python/Django traceback from within a PostgreSQL trigger?

I know that this is not trival since the DB code runs under a different linux process with a different user id.

I am using Python, Django, PostgreSQL, Linux.

I guess this is not easy since the DB trigger runs in a different context than the python interpreter.

Please ask if you need further information.

3 Answers

Answers 1

Is there a way to log the Python/Django traceback from within a PostgreSQL trigger? 

---

No, there is not

• The (SQL) query is executed on the DBMS-server, and so is the code inside the trigger
• The Python code is executed on the client which is a different process, possibly executed by a different user, and maybe even on a different machine.

The only connection between the server (which detects the condition) and the client (which needs to perform the stackdump) is the connected socket. You could try to extend the server's reply (if there is one) by some status code, which is used by the client to stackddump itself. This will only work if the trigger is part of the current transaction, not of some unrelated process.

The other way is: massive logging. Make the DBMS write every submitted SQL to its logfile. This can cause huge amounts of log entries, which you have to inspect.

Answers 2

Given this setup

(django/python) -[SQL connection]-> (PostgreSQL server) 

your intuition that

I guess this is not easy since the DB trigger runs in a different context than the python interpreter.

is correct. At least, we won't be able to do this exactly the way you want it; not without much acrobatics.

However, there are options, each with drawbacks:

• If you are using django with SQLAlchemy, you can register event listeners (either ORM events or Core Events) that detect this bad SQL statement you are hunting, and log a traceback.
• Write a wrapper around your SQL driver, check for the bad SQL statement you are hunting, and log the traceback every time it's detected.

• Give every SQL transaction, or every django request, an ID (could just be some UUID in werkzeug's request-bound storage manager). From here, we gain more options:

  • Configure the logger to log this request ID everywhere, and log all SQL statements in SQLAlchemy. This lets you correlate Django requests, and specific function invocations, with SQL statements. You can do this with echo= in SQLAlchemy.
  • Include this request ID in every SQL statement (extra column?), then log this ID in the PostgreSQL trigger with RAISE NOTICE. This lets you correlate client-side activity in django against server-side activity in PostgreSQL.

• In the spirit of "Test in Production" espoused by Charity Majors, send every request to a sandbox copy of your Django app that reads/writes a sandboxed copy of your production database. In the sandbox database, raise the exception and log your traceback.

  • You can take this idea further and create smaller "async" setups. For example, you can, for each request, trigger a async duplicate (say, with celery) of the same request that hits a DB configured with your PostgreSQL trigger to fail and log the traceback.

• Use RAISE EXCEPTION in the PostgreSQL trigger to rollback the current transaction. In Python, catch that specific exception, log it, then repeat the transaction, changing the data slightly (extra column?) to indicate that this is a retry and the trigger should not fail.

Is there a reason you can't SELECT all row values into Python, then do the detection in Python entirely?

Answers 3

So if you're able to detect the condition after the queries execute, then you can log the condition and/or throw an exception.

Then what you need is tooling like Sentry or New Relic.

Read More

Can not persist data model's field into database, but can retrieve it

I have a problem when trying to persist a data model class into a database. I have a class like this:

class DataModelClass{     //some more field etc.      @Column(name = "number1", nullable = true)     private Integer number1;      @Column(name = "number2", nullable = true)     private Integer number2;      public DataModelClass(){}      (...)      public Integer getNumber2() {         return number2;     }      public void setNumber2( Integer number2 ) {         this.number2= number2;     } } 

The second field was added after first one. When to persist object created with this class via:

em.persist(dataModelClass); 

A new row in database is created, but only with first field added. The second one is empty. When I am debugging the object dataModelClass has every field set with some integer value. When I am adding a value for number2 through pgAdmin, and then retrieving this row with java code via:

DataModelClass dmc = em.find(DataModelClass.class, 1); 

Than dmc.getNumber2() is not empty/null.

Anyone have any ideas what is wrong?

[Edit] Maybe it will help a little more, On data model (DataModelClass) class i got this annotation:

@Entity @Table(name = "custom_table",        uniqueConstraints=@UniqueConstraint(name="UK_example_foreign_id", columnNames={"example_foreign_id"}) ) @SequenceGenerator(name = DataModelClass.SEQ_NAME, sequenceName = DataModelClass.SEQ_NAME, allocationSize = 1) 

Obviously this field exist in my class

2 Answers

Answers 1

I would check if my database is updated as my entity class.

Answers 2

The problem was, that after persist there was another query which was updating the database with null value. So the answer was to change this value in update query. Thanks all.

Read More

How to set lock timeout in postgres - Hibernate

I'm trying to set a Lock for the row I'm working on until the next commit:

entityManager.createQuery("SELECT value from Table where id=:id")             .setParameter("id", "123")             .setLockMode(LockModeType.PESSIMISTIC_WRITE)             .setHint("javax.persistence.lock.timeout", 10000)             .getSingleResult(); 

What I thought should happen is that if two threads will try to write to the db at the same time, one thread will reach the update operation before the other, the second thread should wait 10 seconds and then throw PessimisticLockException.

But instead the thread hangs until the other thread finishes, regardless of the timeout set.

Look at this example :

database.createTransaction(transaction -> {     // Execute the first request to the db, and lock the table     requestAndLock(transaction);      // open another transaction, and execute the second request in     // a different transaction     database.createTransaction(secondTransaction -> {         requestAndLock(secondTransaction);     });      transaction.commit(); }); 

I expected that in the second request the transaction will wait until the timeout set and then throw the PessimisticLockException, but instead it deadlocks forever.

Hibernate generates my request to the db this way :

SELECT value from Table where id=123 FOR UPDATE 

In this answer I saw that Postgres allows only SELECT FOR UPDATE NO WAIT that sets the timeout to 0, but it isn't possible to set a timeout in that way.

Is there any other way that I can use with Hibernate / JPA? Maybe this way is somehow recommended?

3 Answers

Answers 1

I think you could try

SET LOCAL lock_timeout = '10s'; SELECT ....; 

I doubt Hibernate supports this out-of-box. You could try find a way to extend it, not sure if it worth it. Because I guess using locks on a postges database (which is mvcc) is not the smartest option.

You could also do NO WAIT and delay-retry several times from your code.

Answers 2

Hibernate supports a bunch of query hints. The one you're using sets the timeout for the query, not for the pessimistic lock. The query and the lock are independent of each other, and you need to use the hint shown below.

But before you do that, please be aware, that Hibernate doesn't handle the timeout itself. It only sends it to the database and it depends on the database, if and how it applies it.

To set a timeout for the pessimistic lock, you need to use the javax.persistence.lock.timeout hint instead. Here's an example:

entityManager.createQuery("SELECT value from Table where id=:id")         .setParameter("id", "123")         .setLockMode(LockModeType.PESSIMISTIC_WRITE)         .setHint("javax.persistence.lock.timeout", 10000)         .getSingleResult(); 

Answers 3

There is the lock_timeout parameter that does exactly what you want.

You can set it in postgresql.conf or with ALTER ROLE or ALTER DATABASE per user or per database.

Read More

ActiveRecord::StatementInvalid, PG::UndefinedTable error, but generated SQL works

This has been tremendously frustrating. I'm trying to get a has_many through working, and I think I'm just too close to this to see something super obvious. Each step works correctly, and the SQL that Rails is generating works, but together in the console it's not.

The one weird thing about this whole setup is that there are a couple of tables in a salesforce schema, and the tablename and primary key aren't standard. Here's the basic structure:

class Contact   self.table_name =  'salesforce.contact'   self.primary_key = 'sfid'    has_many :content_accesses   has_many :inventories, through: :content_accesses # I've tried inventory and inventorys, just to ensure it's not Rails magic end   class ContentAccess   belongs_to :inventory   belongs_to :contact end   class Inventory   self.table_name =  'salesforce.inventory__c'   self.primary_key = 'sfid'    has_many :content_accesses, foreign_key: 'inventory_id' end 

Works:

c = Contact.first c.content_accesses # works, gives the related items  c.content_accesses.first.inventory # works, gives the related Inventory item 

Error:

c.inventories # Gives:  # ActiveRecord::StatementInvalid (PG::UndefinedTable: ERROR:  relation "content_accesses" does not exist) # LINE 1: ..._c".* FROM "salesforce"."inventory__c" INNER JOIN "content_a... #                                                          ^ # : SELECT  "salesforce"."inventory__c".* FROM "salesforce"."inventory__c" INNER JOIN "content_accesses" ON "salesforce"."inventory__c"."sfid" = "content_accesses"."inventory_id" WHERE "content_accesses"."contact_id" = $1 LIMIT $2 

When I run that query through Postico, though, it works fine. 🤬

Edited to add:

• I moved content_accesses into the salesforce schema, and set self.table_name on the model correctly, but the problem still happens. As such, I don't think this is related to being cross-schema.
• That only makes this problem weirder to me. :(

DDL for the tables:

CREATE TABLE salesforce.inventory__c (     createddate timestamp without time zone,     isdeleted boolean,     name character varying(80),     systemmodstamp timestamp without time zone,     inventory_unique_name__c character varying(255),     sfid character varying(18),     id integer DEFAULT nextval('salesforce.inventory__c_id_seq'::regclass) PRIMARY KEY,     _hc_lastop character varying(32),     _hc_err text );  CREATE UNIQUE INDEX inventory__c_pkey ON salesforce.inventory__c(id int4_ops); CREATE INDEX hc_idx_inventory__c_systemmodstamp ON salesforce.inventory__c(systemmodstamp timestamp_ops); CREATE UNIQUE INDEX hcu_idx_inventory__c_sfid ON salesforce.inventory__c(sfid text_ops);   CREATE TABLE salesforce.contact (     lastname character varying(80),     mailingpostalcode character varying(20),     accountid character varying(18),     assistantname character varying(40),     name character varying(121),     mobilephone character varying(40),     birthdate date,     phone character varying(40),     mailingstreet character varying(255),     isdeleted boolean,     assistantphone character varying(40),     systemmodstamp timestamp without time zone,     mailingstatecode character varying(10),     createddate timestamp without time zone,     mailingcity character varying(40),     salutation character varying(40),     title character varying(128),     mailingcountrycode character varying(10),     firstname character varying(40),     email character varying(80),     sfid character varying(18),     id integer DEFAULT nextval('salesforce.contact_id_seq'::regclass) PRIMARY KEY,     _hc_lastop character varying(32),     _hc_err text );  CREATE UNIQUE INDEX contact_pkey ON salesforce.contact(id int4_ops); CREATE INDEX hc_idx_contact_systemmodstamp ON salesforce.contact(systemmodstamp timestamp_ops); CREATE UNIQUE INDEX hcu_idx_contact_sfid ON salesforce.contact(sfid text_ops);  CREATE TABLE content_accesses (     id BIGSERIAL PRIMARY KEY,     inventory_id character varying(20),     contact_id character varying(20),     created_at timestamp without time zone NOT NULL,     updated_at timestamp without time zone NOT NULL );  CREATE UNIQUE INDEX content_accesses_pkey ON content_accesses(id int8_ops); 

Edit 2: As part of debugging, I've tried running the generated query in the console:

• If I run the generated query using ActiveRecord::Base.connection.execute the query works.
• If I run it through Contact.connection.execute it gives the same error.

It feels like Rails is not figuring something out, but I can't figure out where or why or what.

Edit 3: As requested, the framework trace:

activerecord (5.2.0) lib/active_record/connection_adapters/postgresql_adapter.rb:669:in `prepare' activerecord (5.2.0) lib/active_record/connection_adapters/postgresql_adapter.rb:669:in `block in prepare_statement' /Users/timsullivan/.rvm/rubies/ruby-2.5.1/lib/ruby/2.5.0/monitor.rb:226:in `mon_synchronize' activerecord (5.2.0) lib/active_record/connection_adapters/postgresql_adapter.rb:664:in `prepare_statement' activerecord (5.2.0) lib/active_record/connection_adapters/postgresql_adapter.rb:609:in `exec_cache' activerecord (5.2.0) lib/active_record/connection_adapters/postgresql_adapter.rb:592:in `execute_and_clear' activerecord (5.2.0) lib/active_record/connection_adapters/postgresql/database_statements.rb:81:in `exec_query' activerecord (5.2.0) lib/active_record/connection_adapters/abstract/database_statements.rb:469:in `select_prepared' activerecord (5.2.0) lib/active_record/connection_adapters/abstract/database_statements.rb:55:in `select_all' activerecord (5.2.0) lib/active_record/connection_adapters/abstract/query_cache.rb:101:in `select_all' activerecord (5.2.0) lib/active_record/querying.rb:41:in `find_by_sql' activerecord (5.2.0) lib/active_record/relation.rb:554:in `block in exec_queries' activerecord (5.2.0) lib/active_record/relation.rb:578:in `skip_query_cache_if_necessary' activerecord (5.2.0) lib/active_record/relation.rb:542:in `exec_queries' activerecord (5.2.0) lib/active_record/association_relation.rb:34:in `exec_queries' activerecord (5.2.0) lib/active_record/relation.rb:414:in `load' activerecord (5.2.0) lib/active_record/relation.rb:200:in `records' activerecord (5.2.0) lib/active_record/relation.rb:195:in `to_ary' activerecord (5.2.0) lib/active_record/relation/finder_methods.rb:530:in `find_nth_with_limit' activerecord (5.2.0) lib/active_record/associations/collection_proxy.rb:1136:in `find_nth_with_limit' activerecord (5.2.0) lib/active_record/relation/finder_methods.rb:515:in `find_nth' activerecord (5.2.0) lib/active_record/relation/finder_methods.rb:125:in `first' actionview (5.2.0) lib/action_view/template.rb:159:in `block in render' activesupport (5.2.0) lib/active_support/notifications.rb:170:in `instrument' actionview (5.2.0) lib/action_view/template.rb:354:in `instrument_render_template' actionview (5.2.0) lib/action_view/template.rb:157:in `render' actionview (5.2.0) lib/action_view/renderer/template_renderer.rb:54:in `block (2 levels) in render_template' actionview (5.2.0) lib/action_view/renderer/abstract_renderer.rb:44:in `block in instrument' activesupport (5.2.0) lib/active_support/notifications.rb:168:in `block in instrument' activesupport (5.2.0) lib/active_support/notifications/instrumenter.rb:23:in `instrument' activesupport (5.2.0) lib/active_support/notifications.rb:168:in `instrument' actionview (5.2.0) lib/action_view/renderer/abstract_renderer.rb:43:in `instrument' actionview (5.2.0) lib/action_view/renderer/template_renderer.rb:53:in `block in render_template' actionview (5.2.0) lib/action_view/renderer/template_renderer.rb:61:in `render_with_layout' actionview (5.2.0) lib/action_view/renderer/template_renderer.rb:52:in `render_template' actionview (5.2.0) lib/action_view/renderer/template_renderer.rb:16:in `render' actionview (5.2.0) lib/action_view/renderer/renderer.rb:44:in `render_template' actionview (5.2.0) lib/action_view/renderer/renderer.rb:25:in `render' actionview (5.2.0) lib/action_view/rendering.rb:103:in `_render_template' actionpack (5.2.0) lib/action_controller/metal/streaming.rb:219:in `_render_template' actionview (5.2.0) lib/action_view/rendering.rb:84:in `render_to_body' actionpack (5.2.0) lib/action_controller/metal/rendering.rb:52:in `render_to_body' actionpack (5.2.0) lib/action_controller/metal/renderers.rb:142:in `render_to_body' actionpack (5.2.0) lib/abstract_controller/rendering.rb:25:in `render' actionpack (5.2.0) lib/action_controller/metal/rendering.rb:36:in `render' actionpack (5.2.0) lib/action_controller/metal/instrumentation.rb:46:in `block (2 levels) in render' activesupport (5.2.0) lib/active_support/core_ext/benchmark.rb:14:in `block in ms' /Users/timsullivan/.rvm/rubies/ruby-2.5.1/lib/ruby/2.5.0/benchmark.rb:308:in `realtime' activesupport (5.2.0) lib/active_support/core_ext/benchmark.rb:14:in `ms' actionpack (5.2.0) lib/action_controller/metal/instrumentation.rb:46:in `block in render' actionpack (5.2.0) lib/action_controller/metal/instrumentation.rb:87:in `cleanup_view_runtime' activerecord (5.2.0) lib/active_record/railties/controller_runtime.rb:31:in `cleanup_view_runtime' actionpack (5.2.0) lib/action_controller/metal/instrumentation.rb:45:in `render' actionpack (5.2.0) lib/action_controller/metal/implicit_render.rb:35:in `default_render' actionpack (5.2.0) lib/action_controller/metal/basic_implicit_render.rb:6:in `block in send_action' actionpack (5.2.0) lib/action_controller/metal/basic_implicit_render.rb:6:in `tap' actionpack (5.2.0) lib/action_controller/metal/basic_implicit_render.rb:6:in `send_action' actionpack (5.2.0) lib/abstract_controller/base.rb:194:in `process_action' actionpack (5.2.0) lib/action_controller/metal/rendering.rb:30:in `process_action' actionpack (5.2.0) lib/abstract_controller/callbacks.rb:42:in `block in process_action' activesupport (5.2.0) lib/active_support/callbacks.rb:132:in `run_callbacks' actionpack (5.2.0) lib/abstract_controller/callbacks.rb:41:in `process_action' actionpack (5.2.0) lib/action_controller/metal/rescue.rb:22:in `process_action' actionpack (5.2.0) lib/action_controller/metal/instrumentation.rb:34:in `block in process_action' activesupport (5.2.0) lib/active_support/notifications.rb:168:in `block in instrument' activesupport (5.2.0) lib/active_support/notifications/instrumenter.rb:23:in `instrument' activesupport (5.2.0) lib/active_support/notifications.rb:168:in `instrument' actionpack (5.2.0) lib/action_controller/metal/instrumentation.rb:32:in `process_action' actionpack (5.2.0) lib/action_controller/metal/params_wrapper.rb:256:in `process_action' activerecord (5.2.0) lib/active_record/railties/controller_runtime.rb:24:in `process_action' actionpack (5.2.0) lib/abstract_controller/base.rb:134:in `process' actionview (5.2.0) lib/action_view/rendering.rb:32:in `process' actionpack (5.2.0) lib/action_controller/metal.rb:191:in `dispatch' actionpack (5.2.0) lib/action_controller/metal.rb:252:in `dispatch' actionpack (5.2.0) lib/action_dispatch/routing/route_set.rb:52:in `dispatch' actionpack (5.2.0) lib/action_dispatch/routing/route_set.rb:34:in `serve' actionpack (5.2.0) lib/action_dispatch/journey/router.rb:52:in `block in serve' actionpack (5.2.0) lib/action_dispatch/journey/router.rb:35:in `each' actionpack (5.2.0) lib/action_dispatch/journey/router.rb:35:in `serve' actionpack (5.2.0) lib/action_dispatch/routing/route_set.rb:840:in `call' warden (1.2.7) lib/warden/manager.rb:36:in `block in call' warden (1.2.7) lib/warden/manager.rb:35:in `catch' warden (1.2.7) lib/warden/manager.rb:35:in `call' rack (2.0.5) lib/rack/tempfile_reaper.rb:15:in `call' rack (2.0.5) lib/rack/etag.rb:25:in `call' rack (2.0.5) lib/rack/conditional_get.rb:25:in `call' rack (2.0.5) lib/rack/head.rb:12:in `call' actionpack (5.2.0) lib/action_dispatch/http/content_security_policy.rb:18:in `call' rack (2.0.5) lib/rack/session/abstract/id.rb:232:in `context' rack (2.0.5) lib/rack/session/abstract/id.rb:226:in `call' actionpack (5.2.0) lib/action_dispatch/middleware/cookies.rb:670:in `call' activerecord (5.2.0) lib/active_record/migration.rb:559:in `call' actionpack (5.2.0) lib/action_dispatch/middleware/callbacks.rb:28:in `block in call' activesupport (5.2.0) lib/active_support/callbacks.rb:98:in `run_callbacks' actionpack (5.2.0) lib/action_dispatch/middleware/callbacks.rb:26:in `call' actionpack (5.2.0) lib/action_dispatch/middleware/executor.rb:14:in `call' airbrake (7.2.1) lib/airbrake/rack/middleware.rb:52:in `call' actionpack (5.2.0) lib/action_dispatch/middleware/debug_exceptions.rb:61:in `call' web-console (3.6.1) lib/web_console/middleware.rb:135:in `call_app' web-console (3.6.1) lib/web_console/middleware.rb:30:in `block in call' web-console (3.6.1) lib/web_console/middleware.rb:20:in `catch' web-console (3.6.1) lib/web_console/middleware.rb:20:in `call' actionpack (5.2.0) lib/action_dispatch/middleware/show_exceptions.rb:33:in `call' railties (5.2.0) lib/rails/rack/logger.rb:38:in `call_app' railties (5.2.0) lib/rails/rack/logger.rb:26:in `block in call' activesupport (5.2.0) lib/active_support/tagged_logging.rb:71:in `block in tagged' activesupport (5.2.0) lib/active_support/tagged_logging.rb:28:in `tagged' activesupport (5.2.0) lib/active_support/tagged_logging.rb:71:in `tagged' railties (5.2.0) lib/rails/rack/logger.rb:26:in `call' sprockets-rails (3.2.1) lib/sprockets/rails/quiet_assets.rb:13:in `call' actionpack (5.2.0) lib/action_dispatch/middleware/remote_ip.rb:81:in `call' actionpack (5.2.0) lib/action_dispatch/middleware/request_id.rb:27:in `call' rack (2.0.5) lib/rack/method_override.rb:22:in `call' rack (2.0.5) lib/rack/runtime.rb:22:in `call' activesupport (5.2.0) lib/active_support/cache/strategy/local_cache_middleware.rb:29:in `call' actionpack (5.2.0) lib/action_dispatch/middleware/executor.rb:14:in `call' actionpack (5.2.0) lib/action_dispatch/middleware/static.rb:127:in `call' rack (2.0.5) lib/rack/sendfile.rb:111:in `call' webpacker (3.4.3) lib/webpacker/dev_server_proxy.rb:18:in `perform_request' rack-proxy (0.6.4) lib/rack/proxy.rb:57:in `call' railties (5.2.0) lib/rails/engine.rb:524:in `call' puma (3.11.4) lib/puma/configuration.rb:225:in `call' puma (3.11.4) lib/puma/server.rb:632:in `handle_request' puma (3.11.4) lib/puma/server.rb:446:in `process_client' puma (3.11.4) lib/puma/server.rb:306:in `block in run' puma (3.11.4) lib/puma/thread_pool.rb:120:in `block in spawn_thread' 

1 Answers

Answers 1

Since you say, the generated SQL works when you directly invoke it the problem root lies somewhere in the process of mapping the returned data back to Objects. Even though your setup looks fine, it seems pretty non standard, so I would try giving rails more hints on how the associations belong together.

To start with you should set a source for your through relation (docs):

has_many :inventories, through: :content_accesses, source: :inventory 

If that still does not give rails the right clue, you can try setting inverse_of, foreign_key, primary_key and even class_name on the other belongs_to and has_many associations, to give rails the required hints. It is hard to tell what could possibly help, but in non standard setups you sometimes experience certain problems with automatically inferred names.

Read More

Regex in Postgres to extract full DN in OpenLDAP

I have a program to pass a full string of groups a user in OpenLDAP to Postgres query. The string is exactly like this:

( 'cn=user1,ou=org1,ou=suborg1,o=myorg','cn=user2,ou=org2,ou=suborg1,o=myorg','cn=user3,ou=org1,ou=suborg1,o=myorg','cn=user4,ou=org1,ou=suborg2,o=myorg' ) 

In a query, I only want that to be this in Postgres:

'user1','user3' 

Basically extract value of cn= when the rest of the string is ou=org1,ou=suborg1,o=myorg.

user2 has ou=org2,ou=suborg1,o=myorg which is org2 so it won't match. user4 won't match on suborg2 ,... The variation is unlimited so I like to look for exact match ou=org1,ou=suborg1,o=myorg only.

I know how to do replace but it can't handle unlimited scenarios. Is there a clean way to do this in regexp_replace or regexp_extract?

3 Answers

Answers 1

Probably the cleanest is by using SUBSTRING that can return just the captured substring:

SELECT SUBSTRING(strs FROM 'cn=([^,]+),ou=org1,ou=suborg1,o=myorg') FROM tb1; 

Here, you match cn=, then capture into Group 1 any one or more chars other than , with the negated bracket expression [^,]+ and then match ,ou=org1,ou=suborg1,o=myorg to make sure there is your required right-hand context.

Else, you may try a REGEXP_REPLACE approach, but it will leave the values where no match is found intact:

SELECT REGEXP_REPLACE(strs, '.*cn=([^,]+),ou=org1,ou=suborg1,o=myorg.*', '\1') from tb1; 

It matches any 0+ chars with .*, then cn=, again captures the non-comma chars into Group 1 and then matches ,ou=org1,ou=suborg1,o=myorg and 0+ chars to the end of the string.

See an online PostgreSQL demo:

CREATE TABLE tb1     (strs character varying) ;  INSERT INTO tb1     (strs) VALUES     ('cn=user1,ou=org1,ou=suborg1,o=myorg'),     ('cn=user2,ou=org2,ou=suborg1,o=myorg'),     ('cn=user3,ou=org1,ou=suborg1,o=myorg'),     ('cn=user4,ou=org1,ou=suborg2,o=myorg') ;  SELECT REGEXP_REPLACE(strs, '.*cn=([^,]+),ou=org1,ou=suborg1,o=myorg.*', '\1') from tb1; SELECT substring(strs from 'cn=([^,]+),ou=org1,ou=suborg1,o=myorg') from tb1; 

Results:

Note you may leverage a very useful word boundary \y construct (see Table 9.20. Regular Expression Constraint Escapes) if you do not want to match ocn= with cn=,

'.*\ycn=([^,]+),ou=org1,ou=suborg1,o=myorg\y.*'    ^^                                     ^^ 

Answers 2

You can use regexp_matches() to get all matching cn. Then use string_agg() to build a comma separated list of them.

SELECT string_agg(ldap.cn[1],                   ',') cn        FROM regexp_matches('( ''cn=user1,ou=org1,ou=suborg1,o=myorg'',''cn=user2,ou=org2,ou=suborg1,o=myorg'',''cn=user3,ou=org1,ou=suborg1,o=myorg'',''cn=user4,ou=org1,ou=suborg2,o=myorg'' )',                            '''cn=([^,]*),ou=org1,ou=suborg1,o=myorg''',                            'g') ldap(cn); 

SQL Fiddle

Answers 3

Try regex: (?<=cn=)\w+(?=,ou=org1,ou=suborg1,o=myorg)

Demo

Read More

Postgres Javascript (pg.js) dynamic column names

I have searched and searched and am just not finding the answer to this one.

I am using pg.js to run queries from my Node.js server to a Postgres database. I would like to use 1 function to run queries on two different tables. So I would like to do this:

database.query("SELECT * FROM $1 WHERE $2 = $3;",   [type, idName, parseInt(id, 10)],   (error, result) => {}); 

This results in a syntax error.

error: syntax error at or near "$1" 

I found some SO articles that use either :name or a ~ to cast the variable as a "name". Like this:

database.query("SELECT * FROM $1~ WHERE $2~ = $3;",   [type, idName, parseInt(id, 10)],   (error, result) => {}); 

This results in the same error.

If I hard-code the table name and try to use the ~ on just the column name. I get the error:

error: operator does not exist: unknown ~ 

The only thing that seems to work is this very bad solution:

database.query("SELECT * FROM "+type+" WHERE "+idName+" = $1;",   [parseInt(id, 10)],   (error, result) => {}); 

Any help appreciated.

3 Answers

Answers 1

The problem you are facing is because of interpolation. Dynamic column and table names are not same as injecting dynamic values.

You might want to give this a try:

npm install pg-format  var format = require('pg-format'); var sql = format('SELECT * FROM %I WHERE my_col = %L %s', 'my_table', 34, 'LIMIT 10'); console.log(sql); // SELECT * FROM my_table WHERE my_col = '34' LIMIT 10 

More details here - https://github.com/datalanche/node-pg-format

A dirty ES6 fix could be:

database.query(`SELECT * FROM ${type} WHERE ${idName} = $3;`,       [parseInt(id, 10)],       (error, result) => {}); 

If you plan on building a lot of dynamic queries, consider giving this a try - https://knexjs.org/

Answers 2

I did find a partial solution. While I can not find any documentation about this on the PostGres website, I found some online articles that showed examples of using the shorthand notation for CAST, to cast a string as a column name.

As mentioned in the question, I found several stackoverflow articles that mentioned :name as a solution, but what works for me is ::name which is the shorthand for CAST. Postgres does not document name as a datatype, but this does work for me.

database.query("SELECT * FROM "+ type +" WHERE $1::name = $2;",   [idName, parseInt(id, 10)],   (error, result) => {}); 

The same thing does not work for the table name.

Answers 3

You cannot create prepared statements and cannot inject dynamic table or column names in the query because this would disallow preparing the statement completely. If you definitely have to do prepared statements, you need to know that in PreparedStatements the input datatypes (the dynamic variables) AND the return datatypes (the returned columns) MUST be defined. Since * will return differnet return types for different tables the above will never work. If you know for example that you always return an id and a name you might create a function in postgresql like this:

CREATE TYPE idname AS (id int4, name text);  CREATE OR REPLACE FUNCTION dynamicidnamequery(tablename text,field text,content text)  RETURNS SETOF idname AS  $$ DECLARE      r idname; BEGIN     FOR r IN EXECUTE 'SELECT id,name FROM '||tablename||' WHERE '||field||'='''||content||''''     LOOP         return next r;     END LOOP; END$$ language 'plpgsql';  select * from dynamicidnamequery('company','name','Amazon'); 

The last select can be queried dynamic now.

Read More

Connect to postgres in docker container from host machine

How can I connect to postgres in docker from a host machine?

docker-compose.yml

version: '2'  networks:     database:         driver: bridge services:     app:         build:             context: .             dockerfile: Application.Dockerfile         env_file:             - docker/Application/env_files/main.env         ports:             - "8060:80"         networks:            - database         depends_on:             - appdb      appdb:         image: postdock/postgres:1.9-postgres-extended95-repmgr32         environment:             POSTGRES_PASSWORD: app_pass             POSTGRES_USER: www-data             POSTGRES_DB: app_db             CLUSTER_NODE_NETWORK_NAME: appdb             NODE_ID: 1             NODE_NAME: node1         ports:             - "5432:5432"         networks:             database:                 aliases:                     - database 

docker-compose ps

           Name                          Command               State               Ports ----------------------------------------------------------------------------------------------------- appname_app_1     /bin/sh -c /app/start.sh         Up      0.0.0.0:8060->80/tcp appname_appdb_1   docker-entrypoint.sh /usr/ ...   Up      22/tcp, 0.0.0.0:5432->5432/tcp 

From container I can connect successfully. Both from app container and db container.

List of dbs and users from running psql inside container:

# psql -U postgres psql (9.5.13) Type "help" for help.  postgres=# \du                                        List of roles     Role name     |                         Attributes                         | Member of ------------------+------------------------------------------------------------+-----------  postgres         | Superuser, Create role, Create DB, Replication, Bypass RLS | {}  replication_user | Superuser, Create role, Create DB, Replication             | {}  www-data         | Superuser                                                  | {}  postgres=# \l                                        List of databases       Name      |      Owner       | Encoding |  Collate   |   Ctype    |   Access privileges ----------------+------------------+----------+------------+------------+-----------------------  app_db         | postgres         | UTF8     | en_US.utf8 | en_US.utf8 |  postgres       | postgres         | UTF8     | en_US.utf8 | en_US.utf8 |  replication_db | replication_user | UTF8     | en_US.utf8 | en_US.utf8 |  template0      | postgres         | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +                 |                  |          |            |            | postgres=CTc/postgres  template1      | postgres         | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +                 |                  |          |            |            | postgres=CTc/postgres (5 rows) 

DB image is not official postgres image. But Dockerfile in GitHub seem looking fine.

cat /var/lib/postgresql/data/pg_hba.conf from DB container:

# TYPE  DATABASE        USER            ADDRESS                 METHOD  # "local" is for Unix domain socket connections only local   all             all                                     trust # IPv4 local connections: host    all             all             127.0.0.1/32            trust # IPv6 local connections: host    all             all             ::1/128                 trust # Allow replication connections from localhost, by a user with the # replication privilege. #local   replication     postgres                                trust #host    replication     postgres        127.0.0.1/32            trust #host    replication     postgres        ::1/128                 trust  host all all all md5 host replication replication_user 0.0.0.0/0 md5 

I tried both users with no luck

$ psql -U postgres -h localhost psql: FATAL:  role "postgres" does not exist $ psql -h localhost -U www-data appdb -W Password for user www-data: psql: FATAL:  role "www-data" does not exist 

Looks like on my host machine there is already PSQL running on that port. How can I check it?

5 Answers

Answers 1

I ran this on Ubuntu 16.04

$ psql -h localhost -U www-data app_db Password for user www-data: psql (9.5.13) Type "help" for help.  app_db=# \du                                        List of roles     Role name     |                         Attributes                         | Member of ------------------+------------------------------------------------------------+-----------  postgres         | Superuser, Create role, Create DB, Replication, Bypass RLS | {}  replication_user | Superuser, Create role, Create DB, Replication             | {}  www-data         | Superuser                                                  | {} 

And below from my mac to the VM inside which docker was running (192.168.33.100 is the IP address of the docker VM)

$ psql -h 192.168.33.100 -U www-data app_db Password for user www-data: psql (9.6.9, server 9.5.13) Type "help" for help.  app_db=# \du                                        List of roles     Role name     |                         Attributes                         | Member of ------------------+------------------------------------------------------------+-----------  postgres         | Superuser, Create role, Create DB, Replication, Bypass RLS | {}  replication_user | Superuser, Create role, Create DB, Replication             | {}  www-data         | Superuser                                                  | {} 

They both work for me.

PSQL version on VM

$ psql --version psql (PostgreSQL) 9.5.13 

PSQL version on Mac

$ psql --version psql (PostgreSQL) 9.6.9 

Answers 2

I have a relatively similar setup, and the following works for me to open a psql session on the host machine into the docker postgres instance: docker-compose run --rm db psql -h db -U postgres -d app_development

Where:

• db is the name of the container
• postgres is the name of the user
• app_development is the name of the database

So for you, it would look like docker-compose run --rm appdb psql -h appdb -U www-data -d app_db.

Answers 3

Since you’re running it in OSX, you can always use the pre-installed Network Utility app to run a Port Scan on your host and identify if the postgres server is running (and if yes, on which port).

But I don’t think you have one running on your host. The problem is that Postgres by default runs on 5432 and the docker-compose file that you are trying to run exposes the db container on the same port i.e. 5432. If the Postgres server were already running on your host, then Docker would have tried to expose a a container to a port which is already being used, thereby giving an error.

Another potential solution:
As can be seen in this answer, mysql opens a unix socket with localhost and not a tcp socket. Maybe something similar is happening here.

Try using 127.0.0.1 instead of localhost while connecting to the server in the container.

Answers 4

I believe you have an issue in pg_hba.conf. Here you've specified 1 host that has access - 127.0.0.1/32.

You can change it to this:

# IPv4 local connections: host    all             all             0.0.0.0/0            md5 

This will make sure your host (totally different IP) can connect.

To check if there is an instance of postgresql already running, you can do netstat -plnt | grep 5432. If you get any result from this you can get the PID and verify the process itself.

Answers 5

I believe the problem is you have postgres running on the local machine at port 5432. Issue can be resolved by mapping port 542 of docker container to another port in the host machine. This can be achieved by making a change in docker-compose.yml

Change

"5432:5432"  

to

"5433:5432" 

Now the docker container postgres is running on 5433. You can try connecting to it.

psql -p 5433 -d db_name -U user -h localhost 

Read More

Heroku hangs during migrate with strange pg_advisory_unlock

I'm using Rails 5.1 hosted on Heroku, and I use the following command to migrate my database:

heroku run rake db:migrate -a [my app name]

All the migrations themselves complete correctly:

SQL (1.6ms)  INSERT INTO "schema_migrations" ("version") VALUES ($1) RETURNING "version"  [["version", "20180504164326"]]    (2.1ms)  COMMIT Migrating to AddPinToStaff (20180519024721)    (1.5ms)  BEGIN == 20180519024721 AddPinToStaff: migrating ==================================== -- add_column(:staff, :pin_number, :string)    (4.2ms)  ALTER TABLE "staff" ADD "pin_number" character varying    -> 0.0045s == 20180519024721 AddPinToStaff: migrated (0.0046s) =========================== 

That's the last migration file I created, so it all seems to work. Then, this runs:

SQL (1.6ms)  INSERT INTO "schema_migrations" ("version") VALUES ($1) RETURNING "version"  [["version", "20180519024721"]] (2.4ms)  COMMIT ActiveRecord::InternalMetadata Load (1.7ms)  SELECT  "ar_internal_metadata".* FROM "ar_internal_metadata" WHERE "ar_internal_metadata"."key" = $1 LIMIT $2  [["key", "environment"], ["LIMIT", 1]] (1.3ms)  BEGIN SQL (1.6ms)  INSERT INTO "ar_internal_metadata" ("key", "value", "created_at", "updated_at") VALUES ($1, $2, $3, $4) RETURNING "key"  [["key", "environment"], ["value", "beta"], ["created_at", "2018-06-04 18:54:24.766405"], ["updated_at", "2018-06-04 18:54:24.766405"]] (2.1ms)  COMMIT (1.4ms)  SELECT pg_advisory_unlock(5988010931190918735) 

And it hangs there at that last SELECT statement. What is pg_advisory_unlock and why is it running? Reading this blog post it seems like those should be called from my application somewhere, but I can't find any similar text in my application anywhere. Please help!

1 Answers

Answers 1

pg_advisory_unlock is nothing but a postgresql lock that Heroku is using to obtain a transaction level lock on your database. It is not necessary that if Heroku is unable to obtain the lock the migration itself was unsuccessful. Please check if your schema contains the migrated tables, if yes, you don't need to do anything more. Edit: Your logs say the migrations were commited, which is a strong indication that the migration was indeed successful.

Otherwise, you can try dropping your database and re-creating it, then running the migrations again. If it contains important data already, use heroku pg:backups:capture --app <name-of-app> to backup your database, then run rails db:drop, rails db:create, and finally, rails db:migrate in order to run the migration again. You can restore the database using heroku pg:backups:restore <name-of-backup> --app <name-of-app>

Read More

Find max, min, avg, percentile of count(*) per mmdd PostgreSQL

Postgres version 9.4.18, PostGIS Version 2.2.

Here are the tables I'm working with (and can unlikely make significant changes to the table structure):

Table ltg_data (spans 1988 to 2018):

Column   |           Type           | Modifiers  ----------+--------------------------+----------- intensity | integer                  | not null time      | timestamp with time zone | not null lon       | numeric(9,6)             | not null lat       | numeric(8,6)             | not null ltg_geom  | geometry(Point,4269)     |  Indexes: "ltg_data2_ltg_geom_idx" gist (ltg_geom) "ltg_data2_time_idx" btree ("time")  Size of ltg_data (~800M rows):  ltg=# select pg_relation_size('ltg_data'); pg_relation_size  ------------------ 149729288192 

Table counties:

 Column   |            Type             |                       Modifiers                       -----------+-----------------------------+---------------------------------        ----------------------- gid        | integer                     | not null default         nextval('counties_gid_seq'::regclass) objectid_1 | integer                     |  objectid   | integer                     |  state      | character varying(2)        |  cwa        | character varying(9)        |  countyname | character varying(24)       |  fips       | character varying(5)        |  time_zone  | character varying(2)        |  fe_area    | character varying(2)        |  lon        | double precision            |  lat        | double precision            |  the_geom   | geometry(MultiPolygon,4269) |  Indexes: "counties_pkey" PRIMARY KEY, btree (gid) "counties_gix" gist (the_geom) "county_cwa_idx" btree (cwa) "countyname_cwa_idx" btree (countyname) 

I have a query that calculates the total number of rows per day of the year (month-day) spanning the 30 years. With the help of Stackoverflow, the query to get these counts is working fine. Here's the query and results, using the following function.

Function:

CREATE FUNCTION f_mmdd(date) RETURNS int LANGUAGE sql IMMUTABLE AS $$SELECT to_char($1, 'MMDD')::int$$; 

Query:

SELECT d.mmdd, COALESCE(ct.ct, 0) AS total_count FROM  ( SELECT f_mmdd(d::date) AS mmdd  -- ignoring the year FROM   generate_series(timestamp '2018-01-01'  -- any dummy year                     , timestamp '2018-12-31'                     , interval '1 day') d ) d LEFT  JOIN ( SELECT f_mmdd(time::date) AS mmdd, count(*) AS ct FROM   counties c JOIN   ltg_data d ON ST_contains(c.the_geom, d.ltg_geom) WHERE  cwa = 'MFR' GROUP  BY 1 ) ct USING (mmdd) ORDER  BY 1; 

Results:

mmdd       total_count 725 |        2126 726 |         558 727 |           2 728 |           2 729 |           2 730 |           0 731 |           0 801 |           0 802 |          10 

Desired Results: I'm trying to find other statistical information about the counts for the days of the year. For instance, I know on July 25 (725 in the table below) that the total count over the many years that are in the table is 2126. What I'm looking for is the max daily count for July 25 (725), percent of years that that day is not zero, the min, percent years where count(*) is not zero, percentiles (10th percentile, 25th percentile, 50th percentile, 75th percentile, 90th percentile, and stdev would be useful too). It would be good to see what year the max_daily occurred. I guess if there haven't been any counts for that day in all the years, the year_max_daily would be blank or zero.

mmdd       total_count  max daily  year_max_daily   percent_years_count_not_zero  10th percentile_daily   90th percentile_daily 725 |        2126         1000          1990                 30                          15                   900 726 |         558          120          1992                 20                          10                   80 727 |           2            1          1991                 2                            0                   1 728 |           2            1          1990                 2                            0                   1 729 |           2            1          1989                 2                            0                   1 730 |           0            0                               0                            0                   0  731 |           0            0                               0                            0                   0  801 |           0            0                               0                            0                   0 802 |          10           10          1990                 0                            1                   8 

What I've tried thus far just isn't working. It returns the same results as total. I think it's because I'm just trying to get an avg after the totals have already been calculated, so I'm not really looking at the counts for each day of each year and finding the average.

Attempt:

SELECT AVG(CAST(total_count as FLOAT)), day FROM ( SELECT d.mmdd as day, COALESCE(ct.ct, 0) as total_count FROM ( SELECT f_mmdd(d::date) AS mmdd FROM generate_series(timestamp '2018-01-01', timestamp '2018-12-31',     interval '1 day') d ) d LEFT JOIN (  SELECT mmdd, avg(q.ct) FROM (  SELECT f_mmdd((time at time zone 'utc+12')::date) as mmdd, count(*) as ct FROM counties c JOIN ltg_data d on ST_contains(c.the_geom, d.ltg_geom) WHERE cwa = 'MFR' GROUP BY 1 )   ) as q  ct USING (mmdd) ORDER BY 1 

Thanks for any help!

1 Answers

Answers 1

I haven't included calculations for all requested stats - there is too much in one question, but I hope that you'd be able to extend the query below and add extra stats that you need.

I'm using CTE below to make to query readable. If you want, you can put it all in one huge query. I'd recommend to run the query step-by-step, CTE-by-CTE and examine intermediate results to understand how it works.

CTE_Dates is a simple list of all possible dates for 30 years.

CTE_DailyCounts is a list of basic counts for each day for 30 years (I took your existing query for that).

CTE_FullStats is again a list of all dates together with some stats calculated for each (month,day) using window functions with partitioning by month,day. ROW_NUMBER there is used to get a date where the count was the largest for each year.

Final query selects only one row with the largest count for the year along with the rest of the information.

I didn't try to run the query, because the question doesn't have sample data, so there may be some typos.

WITH CTE_Dates AS (     SELECT         d::date AS dt         ,EXTRACT(MONTH FROM d::date) AS dtMonth         ,EXTRACT(DAY FROM d::date) AS dtDay         ,EXTRACT(YEAR FROM d::date) AS dtYear     FROM         generate_series(timestamp '1988-01-01', timestamp '2018-12-31', interval '1 day') AS d         -- full range of possible dates ) ,CTE_DailyCounts AS (     SELECT         time::date AS dt         ,count(*) AS ct     FROM         counties c         INNER JOIN ltg_data d ON ST_contains(c.the_geom, d.ltg_geom)     WHERE cwa = 'MFR'     GROUP BY time::date ) ,CTE_FullStats AS (     SELECT         CTE_Dates.dt         ,CTE_Dates.dtMonth         ,CTE_Dates.dtDay         ,CTE_Dates.dtYear         ,CTE_DailyCounts.ct         ,SUM(CTE_DailyCounts.ct) OVER (PARTITION BY dtMonth, dtDay) AS total_count         ,MAX(CTE_DailyCounts.ct) OVER (PARTITION BY dtMonth, dtDay) AS max_daily         ,SUM(CASE WHEN CTE_DailyCounts.ct > 0 THEN 1 ELSE 0 END) OVER (PARTITION BY dtMonth, dtDay) AS nonzero_day_count         ,COUNT(*) OVER (PARTITION BY dtMonth, dtDay) AS years_count         ,100.0 * SUM(CASE WHEN CTE_DailyCounts.ct > 0 THEN 1 ELSE 0 END) OVER (PARTITION BY dtMonth, dtDay)          / COUNT(*) OVER (PARTITION BY dtMonth, dtDay) AS percent_years_count_not_zero         ,ROW_NUMBER() OVER (PARTITION BY dtMonth, dtDay ORDER BY CTE_DailyCounts.ct DESC) AS rn     FROM         CTE_Dates         LEFT JOIN CTE_DailyCounts ON CTE_DailyCounts.dt = CTE_Dates.dt ) SELECT     dtMonth     ,dtDay     ,total_count     ,max_daily     ,dtYear AS year_max_daily     ,percent_years_count_not_zero FROM     CTE_FullStats WHERE     rn = 1 ORDER BY     dtMonth     ,dtDay ; 

Read More

Are you able to use a custom Postgres comparison function for ORDER BY clauses?

In Python, I can write a sort comparison function which returns an item in the set {-1, 0, 1} and pass it to a sort function like so:

sorted(["some","data","with","a","nonconventional","sort"], custom_function) 

This code will sort the sequence according to the collation order I define in the function.

Can I do the equivalent in Postgres?

e.g.

SELECT widget FROM items ORDER BY custom_function(widget) 

Edit: Examples and/or pointers to documentation are welcome.

2 Answers

Answers 1

Yes you can, you can even create an functional index to speed up the sorting.

Edit: Simple example:

CREATE TABLE foo(     id serial primary key,     bar int ); -- create some data INSERT INTO foo(bar) SELECT i FROM generate_series(50,70) i; -- show the result SELECT * FROM foo;  CREATE OR REPLACE FUNCTION my_sort(int) RETURNS int  LANGUAGE sql  AS $$     SELECT $1 % 5; -- get the modulo (remainder) $$; -- lets sort! SELECT *, my_sort(bar) FROM foo ORDER BY my_sort(bar) ASC;  -- make an index as well: CREATE INDEX idx_my_sort ON foo ((my_sort(bar))); 

The manual is full of examples how to use your own functions, just start playing with it.

• SQL: http://www.postgresql.org/docs/current/static/xfunc-sql.html
• PL/pgSQL: http://www.postgresql.org/docs/current/static/plpgsql.html

Answers 2

You could do something like this

SELECT DISTINCT ON (interval_alias) *,   to_timestamp(floor((extract('epoch' FROM index.created_at) / 10)) * 10) AT   TIME ZONE 'UTC' AS interval_alias   FROM index   WHERE index.created_at >= '{start_date}'   AND index.created_at <= '{end_date}'   AND product = '{product_id}'   GROUP BY id, interval_alias ORDER BY interval_alias; 

Firstly you define the parameter that will be your ordering column with AS. It could be function or any SQL expression. Then set it to ORDER BY expression and you're done!

In my opinion, this is the smoothest way to do such an ordering.

Read More

When calling DB::select why do I get a “The connection was reset” message?

In my Laravel 5.5 application, calls to DB::select which run a select query on a Postgresql database fail without showing any error in the Apache or Laravel error logs and trigger a "The connection was reset" message. This code sample runs as expected because the function get_users_with_roles exists.

public function missing_function(Request $request) {         try{            $all = DB::select('SELECT * from get_users_with_roles()', []);         }catch(Illuminate\Database\QueryException $qe){             return json_encode($qe->getMessage());         }         return json_encode($all); } 

However, if I replace that SQL string with a function that doesn't exist:

public function missing_function(Request $request) {         try{            $all = DB::select('SELECT * from test()', []);         }catch(Illuminate\Database\QueryException $qe){             return json_encode($qe->getMessage());         }         return json_encode($all); } 

The connection is reset and I can't see any errors in the logs. If I run this erroneous query in a native Postgresql environment:

SELECT * from test(); 

I get a clear error message:

    ERROR:  function test() does not exist LINE 1: select * from test()                       ^ HINT:  No function matches the given name and argument types. You might need to add explicit type casts. 

It is particularly strange because this problem is not consistent. The try block sometimes catches the QueryException and displays the Postgresql error message as excepted.

I have tried adding

php_flag xcache.cacher Off  php_flag xcache.size 0  php_flag xcache.stat Off 

to the .htaccess file but to no avail.

I need the ability to use the DB::select method because I rely heavily on Postgresql user-defined SQL and plpgsql functions in the application. I have a function which constructs the relevant SQL and passes it the DB::select method programmatically, so I need to be able to catch exceptions thrown when there is an error in the SQL, such as when the function is missing.

UPDATE

This problem seems to be with the way DB::select handles any SQL error. I've just tried this out with a function which exists but which throws an SQL error. Again, instead of allowing me to catch this in PHP with a try/catch block, it just resets the connection and doesn't log an error in either the Laravel log or the Apache log.

This question doesn't shed any light. The accepted answer there refers to the expected behaviour. In my environment, the QueryException isn't thrown or caught.

2 Answers

Answers 1

The tricky part of this has been the browser's stubborn refusal to reveal any form of error message. When that happens, I like to go to the command line and try it, thus eliminating the web server as a variable.

From chat, we learned that the command line showed the error as expected, but did not gracefully do so: the error was output, and the script was halted. That's a hard crash, one not attributable to the web server.

With the introduction of \Throwable, the scenarios where PHP dies hard are becoming fewer and farther between. So, in an effort to catch PHP's dying breath, we implemented a register_shutdown_function that pulled error_get_last in an effort to figure out what, if anything, was said just before blowing up.

This revealed, briefly, the error message in the browser (this time using a different browser). However, this was not repeatable. The insight at this point was caching: composer dump-autoload fixed the problem!

I suspect what happened is this:

• Eloquent threw an exception
• PHP was bubbling that up through Laravel's exception handling classes
• At some point, PHP attempted to load a class that wasn't in the autoloader
• PHP crashed hard (this is one of those cases where PHP 7.0 bails)

By running composer dump-autoload, all the "missing" classes were brought into the autoloader's purview and, when tried again, the correct code sequence happened.

Answers 2

i think its an Sql query error

 `SELECT * from test()`  

Since () bracket indicates the function so try to use like

 `SELECT * from test` in your query  

Best way in laravel

Create a model with php artisan make:model Test

Then use in controller like

     `use App\Test;' 

and then to fetch records Test::all(); it will bring all records from database like your requirement SELECT * from Test

Read More

Fresh-installation laravel project can't access postgresql database but can do php migrate

I already asked a question about this 2 days ago, here are the links Got "password authentication failed for user" but in pgAdmin 3 its working

But I still didn't get an answer to solve the problem.

So I tried to create a new laravel project, then edit the .env file, check if php artisan migrate can run.

After I run php artisan migrate it's running, so it means that my credentials to PostgreSQL database are correct right? if not it will tell you password authentication failed for user "postgres", but I don't get any error at all, so I go to the next step. Now after I make sure everything is OK i run php artisan make:auth, it's a success without error at all, so I go to the web browser then run the site, I clicked the register / login button, fill the fields, submit then, it's happened again the nightmare

I got this message from the website

SQLSTATE[08006] [7] FATAL: password authentication failed for user "postgres" FATAL: password authentication failed for user "postgres" (SQL: select count(*) as aggregate from "users" where "email" = test@test.com)

Even though php artisan migrate run really well, so I've no idea why it's happening. Is there somebody that ever run into this problem before? or maybe why it's happening?

I already search all keywords that possible to fix this problem, but I can't found the answer, it's really stressed me out.

for the info I'm using:

PostgreSQL 9.6.8

Laravel 5.6

Ubuntu 17.10

Edited: Here is my pg_hba.conf

1 Answers

Answers 1

Check your database.php file in config directory, and check the pgsql array. If accessing DB credential values from .env not worked there, test it by directly putting credentials there. hope it will help. Artisan commands work by accessing the credentials from .env files directly,not from database.php that's why migration worked.

Read More

SQL: Comparing two tables for missing records and then on the date fields

I have two tables as below

work_assignments

emp_id   | start_date  |   End Date ------------------------------------------   1      | May-10-2017 | May-30-2017   1      | Jun-05-2017 | null   2      | May-08-2017 | null  

hourly_pay

emp_id   | start_date  |   End Date    |  Rate -----------------------------------------------   1      | May-20-2017 | Jun-30-2017   |  75   1      | Jul-01-2017 | null          |  80 

These 2 tables share the emp_id (employee id) foreign key and joining these two I should be able to:

1. find employee records missing in the hourly_pay table. Given the data here, the query should return emp_id 2 from work_assignments table
2. find the records where the hourly_pay start_date that are later than the work assignments start_date. Again, given the data here, the query should return emp_id 1 (because work_assignments.start_date has May-10-2017, while the earliest hourly_pay.start_date is on May-20-2017)

I am able to achieve the first part of result using the join query below

select distinct emp_id from work_contracts left join hourly_pay hr USING(emp_id) where hr.emp_id is null  

I am stuck on the second part where probably I need a correlated subquery to tell the hourly pay table records that did not start before the work_assignments start_date? or is there any other way?

12 Answers

Answers 1

Do the date comparison in an inner query then wrap it to filter it to the ones that satisfy the late pay criteria.

select * from (     select distinct c.emp_id,          case when c.start_date < hr.start_date then 1 else 0 end as latePay     from work_contracts c         left join hourly_pay hr USING(emp_id) ) result where latePay = 1 

Answers 2

You can achieve second part using query

 select distinct wc.emp_id   from (select emp_id, min(start_date) start_date from work_contracts group by emp_id) wc  join (select emp_id, min(start_date) start_date from hourly_pay group by emp_id) hr      on wc.emp_id = hr.emp_id  where wc.start_date < hr.start_date 

Answers 3

This hints at a between condition, with some twists, but I've had extremely bad luck using betweens in joins. They appear to perform some form of cross-join on the back and end then filter out the actual join where-clause style. I know that's not very technical, but I've never done a non-equality condition in a join that's turned out well.

So, this may seem counter-intuitive, but I think exploding all date possibilities might actually be your best bet. Without knowing how big your date ranges actually are it's hard to say.

Also, I think this will actually satisfy both conditions in your question at once -- by telling you all work assignments that do not have corresponding pay rates.

Try this against your actual data and see how it works (and how long it takes).

with pay_dates as (   select     emp_id, rate,     generate_series (start_date, coalesce (end_date, current_date), interval '1 day') as pd   from hourly_pay ), assignment_dates as (   select     emp_id, start_date,     generate_series (start_date, coalesce (end_date, current_date), interval '1 day') as wd   from work_assignments ) select   emp_id, min (wd)::date as from_date,   max (wd)::date as thru_date from   assignment_dates a where   not exists (     select null     from pay_dates p     where p.emp_id = a.emp_id     and a.wd = p.pd   ) group by   emp_id, start_date 

The results should be all work assignment ranges with no rates:

emp     from             thru 1    '2017-05-10'    '2017-05-19' 2    '2017-05-08'    '2017-11-14' 

The cool thing is it would also remove any overlaps, where a work assignment was partially covered.

-- Edit 3/20/2018 --

Per your request, here is a break-down of what the logic does.

with pay_dates as(   select     emp_id, rate,     generate_series (start_date, coalesce (end_date, current_date), interval '1 day') as pd   from hourly_pay ) 

This takes the hourly_pay data and breaks it into a record for each employee, for each day:

emp_id    rate    pay date 1         75      5/20/17 1         75      5/21/17 1         75      5/22/17 ... 1         75      6/30/17 1         80      6/01/17 1         80      6/02/17 ... 1         80      today 

Next,

[implied "with"] assignment_dates as (   select     emp_id, start_date,     generate_series (start_date, coalesce (end_date, current_date), interval '1 day') as wd   from work_assignments ) 

Effectively does the same thing for the work assignments table, only preserving the "start date column" in each row.

Then the main query is this:

select   emp_id, min (wd)::date as from_date,   max (wd)::date as thru_date from   assignment_dates a where   not exists (     select null     from pay_dates p     where p.emp_id = a.emp_id     and a.wd = p.pd   ) group by   emp_id, start_date 

Which draws from the two queries above. The important part is the anti-join:

not exists (   select null   from pay_dates p   where p.emp_id = a.emp_id   and a.wd = p.pd ) 

That identifies every work assignment where there is no corresponding record for that employee, for that day.

So in essence, the query takes the data ranges from both tables, comes up with every possible date combination and then does an anti-join to see where they don't match.

While it seems counterintuitive, to take a single record and blow it up into multiple records, two things to consider:

1. Dates are very bounded creatures -- even in 10 years worth of data that only constitutes 4,000 or so records, which isn't much to a database, even when multiplied by an employee database. Your time frame looks much less than that.

2. I've had very, VERY bad luck using joins other than =, for example between or >. It seems in the background it does cartesians and then filters the results. By comparison, exploding the ranges at least gives you some control over how much data explosion occurs.

For grins, I did it with your sample data above and came up with this, which actually looks accurate:

1   '2017-05-10'    '2017-05-19' 2   '2017-05-08'    '2018-03-20' 

Let me know if any of that is unclear.

Answers 4

I would use not exists/exists:

select wa.empid from work_assignments wa where not exists (select 1 from hourly_pay hp where wa.emp_id = hp.emp_id); 

and for the second:

select wa.* from work_assignments wa where not exists (select 1                   from hourly_pay hp                   where wa.emp_id = hp.emp_id and ep.start_date <= wp.start_date                  ); 

The question is very particular on (2). However, I would expect that you would want hourly pay for the entire period of the assignment, not just the start date. If that is the case, then the OP should ask a new qustion.

Answers 5

You can solve this with using the daterange type (because, what you basically want is the missing ranges in hourly_pay table.).

I used the following operators in it:

• + range union
• - range subtraction
• && test for range intersection
• @> test for range containment

With these and a simple left join, you can write a query to find out which ranges are missing in the hourly_pay table.

select     wa.emp_id, lower(dr) start_date, upper(dr) - 1 end_date from       work_assignments wa left join  hourly_pay hp on wa.emp_id = hp.emp_id and        daterange(wa.start_date, wa.end_date, '[]') && daterange(hp.start_date, hp.end_date, '[]') cross join lateral (select case                       when hp is null then daterange(wa.start_date, wa.end_date, '[]')                       else daterange(wa.start_date, wa.end_date, '[]')                          + daterange(hp.start_date, hp.end_date, '[]')                          - daterange(hp.start_date, hp.end_date, '[]')                     end dr) dr where      not exists (select 1                        from   hourly_pay p                        where  p.emp_id = wa.emp_id                        and    daterange(p.start_date, p.end_date, '[]') @> dr)  -- emp_id | start_date | end_date ----------+------------+------------- -- 1      | 2017-05-01 | 2017-05-19 -- 2      | 2017-05-08 | (null) 

http://sqlfiddle.com/#!17/4bac0/14

Answers 6

Maybe I am a little caught up by the wording, but would this not suffice? This would return any emp_id where there is a record for which the hourly start date is after a work assignment start date

select distinct wc.emp_id from work_contracts wc left join hourly_pay hr USING(emp_id) where hr.start_date > wc.start_date 

Answers 7

select distinct p.emp_id <br> from hourly_pay p <br> join work_assignments w on p.emp_id = w.emp_id <br> where p.start_date < w.start_date <br> 

Based on the stated requirement in the original question: find the records where the hourly_pay start_date that are later than the work assignments start_date. Again, given the data here, the query should return emp_id 1 (because work_assignments.start_date has May-10-2017, while the earliest hourly_pay.start_date is on May-20-2017)

This means to me that they only want the employee id number.

Answers 8

Second query is very simple,

Try below query

select distinct h.emp_id  from work_assignments w inner join hourly_pay h  on  w.emp_id = h.emp_id and h.start_date > w.start_date; 

Answers 9

Looking at your data, I can make following assumptions:

1) There can be max one record for an employee that has end_date as null this condition applied to both tables.

2) Multiple records dates for same employee don't overlap When employee has multiple records (like Emp 1) , he/she can't have dates like [jan 1 - feb 1] and next record as [jan 15-feb 20] or [jan 15 - null] (they must be for non overlapping periods).

With these in mind, below query should work for you.

SELECT hourly_pay.* FROM work_assignments INNER JOIN hourly_pay  USING(emp_id) WHERE hourly_pay.start_date > work_assignments.start_date         AND ( hourly_pay.start_date < work_assignments.end_date              OR (work_assignments.end_date is null                    AND hourly_pay.end_date is null) );  

Explanation: The query joins both tables on emp_id then filters records that

1) Have start_date in hourly_pay > start_date in work_assignments

-AND-

2) Have start_date in hourly_pay < end_date in work_assignments (This is needed, so we can avoid comparing un-related time period records from both tables

-OR-

End dates of both table records are null, using assumption 1 (stated above) there can be max one record for an employee that has end_date as null.

Based on your data, this query should return both records of EMP 1 in hourly_pay as start_date there is > start_date in work_assignments.

If you just need list of EMP IDs you can just select that column SELECT DISTINCT hourly_pay.emp_id ...(rest of the query)

Answers 10

http://sqlfiddle.com/#!17/f4595/1

1. Records missing in hourly_pay table;

Instead of using left join and then filtering null valued records, I suggest you to use not exists, It will work way faster.

    SELECT w.emp_id, 'missing in the hourly_pay table' FROM work_assignments w     WHERE NOT exists (SELECT 1 FROM hourly_pay h WHERE h.emp_id = w.emp_id) 

2. Records hourly_pay start_date is later than the work assignment start_date;

   SELECT w.emp_id FROM work_assignments w WHERE NOT exist (     SELECT 1 FROM hourly_pay hp     WHERE         hp.start_date < w.start_date AND w.emp_id = hp.emp_id ) 

Second query actually includes the results from first query, so you can merge them like below:

SELECT     w.emp_id,     (CASE WHEN ( EXISTS             (SELECT 1 FROM hourly_pay h                 WHERE                     h.emp_id = w.emp_id ) )            THEN             'hourly_pay start_date is later'           ELSE             'missing in the hourly_pay table'           END) FROM     work_assignments w WHERE     NOT EXISTS (         SELECT             1         FROM             hourly_pay hp         WHERE             hp.start_date < w.start_date         AND w.emp_id = hp.emp_id     ) 

Answers 11

this will do the job nicely.

SELECT DISTINCT emp_id  FROM work_assingment  JOIN hourly_pay hr USING(emp_id) WHERE hr.start_date < work_assingment.start_date; 

Answers 12

If i understand correctly below query should work;

• first join is getting hourly_pays which has bigger date then work assignments

• second join is checking until it founds the earliest hour from hourly_pay table

• the first left join can be avoided if you dont want to see employees which has no data in hourly_pay table [emp_id = 2]

  select h.emp_id,h.start_date from work_assignments hr  left join hourly_pay h on hr.emp_id=h.emp_id and hr.start_date < h.start_date  left join hourly_pay h2 on h2.emp_id = h.emp_id and h.start_date > h2.start_date  where h2.start_date is null 

Read More

Combine different rows of same table - Postgres

We have a table that saves information about the interval of the employees. Let's call it INTERVAL_TABLE.

We save when the user starts a interval and when he finishes. The user can start a interval as many times as he wants and finish as many times as he wants as well.

This is a simplified structure of the INTERVAL_TABLE:

   INTERVAL_ID | USER_ID | INTERVAL_TYPE_ID | INTERVAL_TIMESTAMP | ENTRY_TYPE 

A user may have these entries in the table:

Now, we must create a report combining different entries of that table that refer to the same user and interval type. We should be able to identify intervals that have a start and an end and group these two in one row. Assuming the data in the image above, the output of the report should be the following:

The output should be ordered by date, like the above image.

I have no idea how to create a query to do that.

Thanks!

Edit - Extra info:

To find the END interval for any INIT interval, we should find the closest END interval based on the timestamp of that interval. That's how we know we should match ID 1 with ID 2 and not with ID 3.

It's important to note that if a INIT interval is followed by another INIT interval (based on the timestamps), we should not proceed to find the END for that INIT. That is because this is a INIT without END.

4 Answers

Answers 1

DBFiddle

This might not be the most efficient way to do this (I imagine a recursive query might be), but I find these subqueries easier to maintain:

WITH ordered_table AS (   SELECT row_number() OVER(ORDER BY INTERVAL_TIMESTAMP ASC) row_num, *   FROM INTERVAL_TABLE   ORDER BY row_num ),  _inits AS (   SELECT     t1.USER_ID,     t1.INTERVAL_TYPE_ID      AS INTERVAL_TYPE,     t1.INTERVAL_TIMESTAMP    AS INTERVAL_TIMESTAMP_INIT,     CASE       WHEN t1.ENTRY_TYPE = 'INIT_INTERVAL' AND t2.ENTRY_TYPE = 'END_INTERVAL'        THEN t2.INTERVAL_TIMESTAMP      END                      AS INTERVAL_TIMESTAMP_END,     t1.INTERVAL_ID           AS INTERVAL_ID_INIT,     CASE       WHEN t1.ENTRY_TYPE = 'INIT_INTERVAL' AND t2.ENTRY_TYPE = 'END_INTERVAL'        THEN t2.INTERVAL_ID      END                      AS INTERVAL_ID_END   FROM      ordered_table AS t1   LEFT JOIN ordered_table AS t2 ON (t1.row_num = t2.row_num - 1)   WHERE t1.ENTRY_TYPE = 'INIT_INTERVAL' ),  _ends AS (   SELECT     t1.USER_ID,     t1.INTERVAL_TYPE_ID      AS INTERVAL_TYPE,     NULL::timestamp          AS INTERVAL_TIMESTAMP_INIT,     CASE       WHEN t1.ENTRY_TYPE = 'END_INTERVAL' AND t2.ENTRY_TYPE = 'END_INTERVAL'        THEN t2.INTERVAL_TIMESTAMP      END                      AS INTERVAL_TIMESTAMP_END,     NULL::int                AS INTERVAL_ID_INIT,     t2.INTERVAL_ID           AS INTERVAL_ID_END   FROM       ordered_table AS t1   RIGHT JOIN ordered_table AS t2 ON (t1.row_num = t2.row_num - 1)   WHERE t2.ENTRY_TYPE = 'END_INTERVAL' )  SELECT * FROM (     SELECT * FROM _inits     UNION ALL     SELECT * FROM _ends ) qry WHERE COALESCE(interval_timestamp_init, interval_timestamp_end) IS NOT NULL ORDER BY COALESCE(interval_timestamp_init, interval_timestamp_end) 

Basically, INITs will always be listed. They will either have an associated END or a null. So almost all the content from _inits will be there.

Because the ENDs were already captured by the INITs, we only need to capture the ones that don't have an INIT (they were preceded by an END).

Because they are outer joins, you simply can remove the cases where INIT and END both are NULL and apply proper ordering.

Answers 2

It could be done easy and efficiently using LEAD and LAG functions. At least it is much more efficient than self-join of the table: O(n) vs O(n*n).

At first add columns for the next and previous row using LEAD and LAG with appropriate PARTITION BY.

Then build two sets of pairs - the first that starts with INIT_INTERVAL, the second that ends with END_INTERVAL. If there is a pair that has both Init and End - it will be included twice and later eliminated in UNION.

SQL Fiddle

Sample data (this is something that you should have included in your question in addition to the screenshot)

CREATE TABLE INTERVAL_TABLE (   INTERVAL_ID int,   USER_ID int,   INTERVAL_TYPE_ID int,   INTERVAL_TIMESTAMP timestamp,   ENTRY_TYPE varchar(255));  INSERT INTO INTERVAL_TABLE (INTERVAL_ID, USER_ID, INTERVAL_TYPE_ID, INTERVAL_TIMESTAMP, ENTRY_TYPE) VALUES (1, 1, 1, '2018-03-08 14:00:00', 'INIT_INTERVAL'), (2, 1, 1, '2018-03-08 15:00:00', 'END_INTERVAL' ), (3, 1, 1, '2018-03-08 15:30:00', 'END_INTERVAL' ), (4, 1, 1, '2018-03-08 15:45:00', 'INIT_INTERVAL'), (5, 1, 1, '2018-03-08 15:50:00', 'INIT_INTERVAL'); 

Query

WITH CTE AS (   SELECT     USER_ID     ,INTERVAL_TYPE_ID     ,ENTRY_TYPE AS Curr_Entry_Type     ,INTERVAL_TIMESTAMP AS Curr_Interval_Timestamp     ,INTERVAL_ID AS Curr_Interval_ID      ,LAG(ENTRY_TYPE) OVER(PARTITION BY USER_ID, INTERVAL_TYPE_ID ORDER BY INTERVAL_TIMESTAMP) AS Prev_Entry_Type     ,LAG(INTERVAL_TIMESTAMP) OVER(PARTITION BY USER_ID, INTERVAL_TYPE_ID ORDER BY INTERVAL_TIMESTAMP) AS Prev_Interval_Timestamp     ,LAG(INTERVAL_ID) OVER(PARTITION BY USER_ID, INTERVAL_TYPE_ID ORDER BY INTERVAL_TIMESTAMP) AS Prev_Interval_ID      ,LEAD(ENTRY_TYPE) OVER(PARTITION BY USER_ID, INTERVAL_TYPE_ID ORDER BY INTERVAL_TIMESTAMP) AS Next_Entry_Type     ,LEAD(INTERVAL_TIMESTAMP) OVER(PARTITION BY USER_ID, INTERVAL_TYPE_ID ORDER BY INTERVAL_TIMESTAMP) AS Next_Interval_Timestamp     ,LEAD(INTERVAL_ID) OVER(PARTITION BY USER_ID, INTERVAL_TYPE_ID ORDER BY INTERVAL_TIMESTAMP) AS Next_Interval_ID   FROM     INTERVAL_TABLE ) ,CTE_Result AS (   SELECT     USER_ID     ,INTERVAL_TYPE_ID     ,Curr_Entry_Type AS Entry_Type_Init     ,Curr_Interval_Timestamp AS Interval_Timestamp_Init     ,Curr_Interval_ID AS Interval_ID_Init     ,Next_Entry_Type AS Entry_Type_End     ,CASE WHEN Next_Entry_Type = 'END_INTERVAL' THEN Next_Interval_Timestamp END AS Interval_Timestamp_End     ,CASE WHEN Next_Entry_Type = 'END_INTERVAL' THEN Next_Interval_ID END AS Interval_ID_End   FROM CTE   WHERE Curr_Entry_Type = 'INIT_INTERVAL'    UNION -- sic! not UNION ALL    SELECT     USER_ID     ,INTERVAL_TYPE_ID     ,Prev_Entry_Type AS Entry_Type_Init     ,CASE WHEN Prev_Entry_Type = 'INIT_INTERVAL' THEN Prev_Interval_Timestamp END AS Interval_Timestamp_Init     ,CASE WHEN Prev_Entry_Type = 'INIT_INTERVAL' THEN Prev_Interval_ID END AS Interval_ID_Init     ,Curr_Entry_Type AS Entry_Type_End     ,Curr_Interval_Timestamp AS Interval_Timestamp_End     ,Curr_Interval_ID AS Interval_ID_End   FROM CTE   WHERE Curr_Entry_Type = 'END_INTERVAL' ) SELECT     USER_ID     ,INTERVAL_TYPE_ID     ,Interval_Timestamp_Init     ,Interval_Timestamp_End     ,Interval_ID_Init     ,Interval_ID_End FROM CTE_Result ORDER BY   USER_ID   ,INTERVAL_TYPE_ID   ,COALESCE(Interval_Timestamp_Init, Interval_Timestamp_End) 

Results

| user_id | interval_type_id | interval_timestamp_init | interval_timestamp_end | interval_id_init | interval_id_end | |---------|------------------|-------------------------|------------------------|------------------|-----------------| |       1 |                1 |    2018-03-08T14:00:00Z |   2018-03-08T15:00:00Z |                1 |               2 | |       1 |                1 |                  (null) |   2018-03-08T15:30:00Z |           (null) |               3 | |       1 |                1 |    2018-03-08T15:45:00Z |                 (null) |                4 |          (null) | |       1 |                1 |    2018-03-08T15:50:00Z |                 (null) |                5 |          (null) | 

Answers 3

You can use the INTERVAL_ID (or a new column with a generated row_number) to join two instances of the same table, using as predicate something like this:

on a.INTERVAL_ID=b.INTERVAL_ID + 1 

This way, you can compare and get in 1 line each record with the next one.

Answers 4

This query gives the output you need:

WITH Intervals AS (     WITH Events AS     (         WITH OrderedEvents AS         (             SELECT INTERVAL_ID, USER_ID, INTERVAL_TYPE_ID, INTERVAL_TIMESTAMP, ENTRY_TYPE, row_number() over (partition by USER_ID, INTERVAL_TYPE_ID order by INTERVAL_TIMESTAMP ASC) AS EVENT_ORDER FROM INTERVAL_TABLE             UNION ALL             SELECT NULL AS INTERVAL_ID, USER_ID, INTERVAL_TYPE_ID, NULL AS INTERVAL_TIMESTAMP, 'INIT_INTERVAL' AS ENTRY_TYPE, 0 AS EVENT_ORDER FROM INTERVAL_TABLE GROUP BY USER_ID, INTERVAL_TYPE_ID             UNION ALL             SELECT NULL AS INTERVAL_ID, USER_ID, INTERVAL_TYPE_ID, NULL AS INTERVAL_TIMESTAMP, 'END_INTERVAL' AS ENTRY_TYPE, COUNT(*) + 1 AS EVENT_ORDER FROM INTERVAL_TABLE GROUP BY USER_ID, INTERVAL_TYPE_ID         )         SELECT Events1.USER_ID, Events1.INTERVAL_TYPE_ID, Events1.INTERVAL_TIMESTAMP AS INTERVAL_TIMESTAMP_INIT, Events2.INTERVAL_TIMESTAMP AS INTERVAL_TIMESTAMP_END, Events1.INTERVAL_ID AS INTERVAL_ID_INIT, Events2.INTERVAL_ID  AS INTERVAL_ID_END, Events1.ENTRY_TYPE AS ENTRY_TYPE1, Events2.ENTRY_TYPE AS ENTRY_TYPE2         FROM OrderedEvents Events1 INNER JOIN         OrderedEvents Events2         ON Events1.USER_ID = Events2.USER_ID AND Events1.INTERVAL_TYPE_ID = Events2.INTERVAL_TYPE_ID AND Events1.EVENT_ORDER + 1 = Events2.EVENT_ORDER     )     SELECT USER_ID, INTERVAL_TYPE_ID,        CASE WHEN ENTRY_TYPE1 = 'INIT_INTERVAL' AND ENTRY_TYPE2 = 'END_INTERVAL' THEN INTERVAL_TIMESTAMP_INIT            WHEN ENTRY_TYPE1 = 'INIT_INTERVAL' AND ENTRY_TYPE2 = 'INIT_INTERVAL' THEN INTERVAL_TIMESTAMP_INIT            WHEN ENTRY_TYPE1 = 'END_INTERVAL' AND ENTRY_TYPE2 = 'END_INTERVAL' THEN NULL       END AS INTERVAL_TIMESTAMP_INIT,        CASE WHEN ENTRY_TYPE1 = 'INIT_INTERVAL' AND ENTRY_TYPE2 = 'END_INTERVAL' THEN INTERVAL_TIMESTAMP_END            WHEN ENTRY_TYPE1 = 'INIT_INTERVAL' AND ENTRY_TYPE2 = 'INIT_INTERVAL' THEN NULL            WHEN ENTRY_TYPE1 = 'END_INTERVAL' AND ENTRY_TYPE2 = 'END_INTERVAL' THEN INTERVAL_TIMESTAMP_END       END AS INTERVAL_TIMESTAMP_END,        CASE WHEN ENTRY_TYPE1 = 'INIT_INTERVAL' AND ENTRY_TYPE2 = 'END_INTERVAL' THEN INTERVAL_ID_INIT            WHEN ENTRY_TYPE1 = 'INIT_INTERVAL' AND ENTRY_TYPE2 = 'INIT_INTERVAL' THEN INTERVAL_ID_INIT            WHEN ENTRY_TYPE1 = 'END_INTERVAL' AND ENTRY_TYPE2 = 'END_INTERVAL' THEN NULL       END AS INTERVAL_ID_INIT,        CASE WHEN ENTRY_TYPE1 = 'INIT_INTERVAL' AND ENTRY_TYPE2 = 'END_INTERVAL' THEN INTERVAL_ID_END            WHEN ENTRY_TYPE1 = 'INIT_INTERVAL' AND ENTRY_TYPE2 = 'INIT_INTERVAL' THEN NULL            WHEN ENTRY_TYPE1 = 'END_INTERVAL' AND ENTRY_TYPE2 = 'END_INTERVAL' THEN INTERVAL_ID_END       END AS INTERVAL_ID_END      FROM Events ) SELECT * FROM Intervals WHERE INTERVAL_ID_INIT IS NOT NULL OR INTERVAL_ID_END IS NOT NULL; 

At first, we build OrderedEvents CTE that groups entries by USER_ID and INTERVAL_TYPE_ID, sorts them by INTERVAL_TIMESTAMP within each group and assign numeric order to each event. Also for each group we add INIT_INTERVAL as first event and END_INTERVAL as last event to cover cases when group starts with END_INTERVAL or finishes with INIT_INTERVAL:

WITH OrderedEvents AS (     SELECT INTERVAL_ID, USER_ID, INTERVAL_TYPE_ID, INTERVAL_TIMESTAMP, ENTRY_TYPE, row_number() over (partition by USER_ID, INTERVAL_TYPE_ID order by INTERVAL_TIMESTAMP ASC) AS EVENT_ORDER FROM INTERVAL_TABLE     UNION ALL     SELECT NULL AS INTERVAL_ID, USER_ID, INTERVAL_TYPE_ID, NULL AS INTERVAL_TIMESTAMP, 'INIT_INTERVAL' AS ENTRY_TYPE, 0 AS EVENT_ORDER FROM INTERVAL_TABLE GROUP BY USER_ID, INTERVAL_TYPE_ID     UNION ALL     SELECT NULL AS INTERVAL_ID, USER_ID, INTERVAL_TYPE_ID, NULL AS INTERVAL_TIMESTAMP, 'END_INTERVAL' AS ENTRY_TYPE, COUNT(*) + 1 AS EVENT_ORDER FROM INTERVAL_TABLE GROUP BY USER_ID, INTERVAL_TYPE_ID ) SELECT * FROM OrderedEvents ORDER BY user_id, interval_type_id, event_order; 

This query gives following results for the provided data:

Then we intersect OrderedEvents with itself on USER_ID and INTERVAL_TYPE_ID and select pairs of neighbor events (Events1.EVENT_ORDER + 1 = Events2.EVENT_ORDER):

WITH OrderedEvents AS (     ... ) SELECT Events1.USER_ID, Events1.INTERVAL_TYPE_ID, Events1.INTERVAL_TIMESTAMP AS INTERVAL_TIMESTAMP_INIT, Events2.INTERVAL_TIMESTAMP AS INTERVAL_TIMESTAMP_END, Events1.INTERVAL_ID AS INTERVAL_ID_INIT, Events2.INTERVAL_ID  AS INTERVAL_ID_END, Events1.ENTRY_TYPE AS ENTRY_TYPE1, Events2.ENTRY_TYPE AS ENTRY_TYPE2 FROM OrderedEvents Events1 INNER JOIN OrderedEvents Events2 ON Events1.USER_ID = Events2.USER_ID AND Events1.INTERVAL_TYPE_ID = Events2.INTERVAL_TYPE_ID AND Events1.EVENT_ORDER + 1 = Events2.EVENT_ORDER 

This query gives following results:

Now we should transform these pairs of neighbor events to intervals based on the logic you described. Previous output has columns entry_type1 and entry_type2 which could take values of INIT_INTERVAL or END_INTERVAL. The possible combinations are:

• <INIT_INTERVAL, END_INTERVAL> - this is the most natural case when INIT_INTERVAL is followed by END_INTERVAL. We take event values as is.
• <INIT_INTERVAL(1), INIT_INTERVAL(2)> - the case of two consecutive INIT_INTERVAL. We force ending of the interval by taking <INIT_INTERVAL(1), NULL>. INIT_INTERVAL(2) will be taken with the next pair when it will be in the first entry.
• <END_INTERVAL(1), END_INTERVAL(2)> - the case of two consecutive END_INTERVAL. We force start of the interval by taking <NULL, END_INTERVAL(2)>. END_INTERVAL(1) is processed either by case #1 or by the current case when it is the second entry in the pair.
• <END_INTERVAL, INIT_INTERVAL> - such pairs are just skipped. END_INTERVAL is taken either by case #1 or case #3. INIT_INTERVAL is taken either by case #1 or case #2.

All this logic is put into set of CASE expressions. There are 4 such expressions with duplicated conditions, because we conditionally select 4 different columns (INTERVAL_TIMESTAMP_INIT, INTERVAL_TIMESTAMP_END, INTERVAL_ID_INIT and INTERVAL_ID_END) which could not be done with one CASE expression.

The final output is the same as you described:

Read More