Hi, would it be possible to switch the benchmark from just wrapping the single psycopg execution in time.time() to using an in-SQL benchmark? The one I've been using for tests is:

CREATE OR REPLACE FUNCTION bench(query TEXT, iterations INTEGER = 500)
RETURNS TABLE(median FLOAT) AS $$
DECLARE
  _start TIMESTAMPTZ;
  _end TIMESTAMPTZ;
  _delta DOUBLE PRECISION;
BEGIN
  CREATE TEMP TABLE IF NOT EXISTS _bench_results (
      elapsed DOUBLE PRECISION
  );

  -- Warm the cache
  FOR i IN 1..5 LOOP
    EXECUTE query;
  END LOOP;

  -- Run test and collect elapsed time into _bench_results table
  FOR i IN 1..iterations LOOP
    _start = clock_timestamp();
    EXECUTE query;
    _end = clock_timestamp();
    _delta = 1000 * ( extract(epoch from _end) - extract(epoch from _start) );
    INSERT INTO _bench_results VALUES (_delta);
  END LOOP;

  RETURN QUERY SELECT percentile_cont(0.5) WITHIN GROUP (ORDER BY elapsed) FROM _bench_results;
  DROP TABLE IF EXISTS _bench_results;

END
$$
LANGUAGE plpgsql;

then executing with:

cursor.execute(f"SELECT median FROM bench(%s)", (query,))
result = cursor.fetchone()
print(f"Query #{str(i).zfill(2)} took {round(result[0], 2)}ms")

The benefit is that one execution time spiking because of some Python performance annoyance isn't possible, because the execution time is derived from multiple executions from within the SQL engine.

Then for the actual query correctness results the regular execution can still be done and written to a CSV

Should we change the dbname, password, host and port in queries.py?
There are three places where these parameters are used.
If we don't change these default settings, an error will occur:
connection failed: FATAL: password authentication failed for user "postgres"
connection to server at "127.0.0.1", port 5432 failed: FATAL: password authentication failed for user "postgres"

Dear Gabriel.

Having an issue running query number 1. When I run the queries on the created database using PGAdmin it works absolutely no problem. No errors, no warnings.

The moment I insert the query into the queries.py file, it tells me that the "match" table does not exist. This is impossible as it exists in the DB, and I ran the queries in PgAdmin with no issue as described before.

The DB name is project_database, as was instructed, and I am running the code on a VM with Ubuntu, everything else is working.

Not sure what to do here, is there a step I am missing? I have inserted my query directly into the string for the query in queries.py.

Please any help would be appreciated, I am very confused, again the query functions fine in PgAdmin

Hey Gabe, for our final project submission we compressed the dbexport as instructed, however when attempting to run it on the course VM, I run into this error:

None of our groupmates really understand how github works in this capacity, do you know what this error means or how we can fix it?

Just to make sure I'm doing it correctly:

• I've created an empty database called "project_database"
• then, exported the main database (populated using the json files) to a file called query_database
• then deleted dbexport.sql file and replaced "dbexport.sql" in load_database(cursor, conn) with "query_database"
• then added queries to Q_n functions

The third step isn't mentioned in README or project document but I found it necessary. Is this how it should be?

Hello,

I have been attempting to test my queries with the provided queries.py on a Linux environment. Although the work I have done across the term was done entirely on Windows, I believe I have set up psycog3 and PostgreSQL correctly on a Ubuntu VM, however I am consistently running into problems with running the file. The current issue I am having is an authentication error, despite modifying passwords in the queries.py file to those of my previous databases (they would be changed back to '1234' before submission). In case this is relevant, the database setup script, queries.py, and exported database were all made/worked on in Windows and later transfered to Linux. Any guidance towards how to effectively execute the file, or any essential knowledge on the setup and usage of PostgreSQL on Linux that might easily be missed, would be appreciated.

Thanks

Could you include instructions on what versions of psycopg is needed and how to install it

I'm running into errors when dbexport.sql is ran by the script because my project path has parenthesis in it. Would you be able to add quotes around the path to avoid this?

# BEFORE
command = f'psql -h {host} -U {user} -d {query_database_name} -a -f {os.path.join(dir_path, "dbexport.sql")}'

# AFTER
command = f'psql -h {host} -U {user} -d {query_database_name} -a -f \'{os.path.join(dir_path, "dbexport.sql")}\''

Thanks!

Hello!

I've been trying to run the queries.py file but have been receiving:
[ERROR] Error getting time. current transaction is aborted, commands ignored until end of transaction block

After adding a debug statement to find the exception I received the output:
relation "players" does not exist
LINE 3: FROM players p
^
[ERROR] Error getting time.
current transaction is aborted, commands ignored until end of transaction block

I have a database named "project_database" that contains the players table. Additionally, I ran the query in postgresql and it ran with no errors. I was wondering if there was any reason as for why the python file isn't seeing the table.

Thank you!

Could you modify the write_csv function in the autograder with the following:

# with open(filename, 'w', newline='') as csvfile:
with open(filename, 'w', encoding='utf-8', newline='') as csvfile:

The default for me is not utf-8. Without the change I was getting errors for all non-ascii names.

Thanks

Hi there,
Just wanted to clarify a few things.

1. My .SQL file is larger than GitHub's maximum 100mb single file limit so I had to compress it. Can I assume that the TA will unzip it before running the autograder?
2. The README states that any other files in our submission other than queries.py, dbexport.SQL, and the json_loader folder with our database creation script inside will void the entire submission. But we also need to submit a project report PDF and diagram image files for the project. Are we just intended to put our code-related files in a sub-folder in our repo or something?
3. Does queries.py judge query correctness? It gives us runtimes but I'm not sure if the speeds are actually good or if the queries are returning as expected.

Thanks for your time,
Joey V.

When running queries.py I was getting the error: "'psql' is not recognized as an internal or external command, operable program or batch file.". I believe this is because the environment variables were not being used while initializing the subprocess.

To solve this issue, I've change the code from:

env = {'PGPASSWORD': password}
subprocess.run(command, shell=True, check=True, env=env)

To:

env = os.environ.copy()
env['PGPASSWORD'] = password
subprocess.run(command, shell=True, check=True, env=env)

This resolves this issue on my machine.

Hey just posting this here, having some issues running pdadmin4 on the VM.

It was working fine yesterday but now I get this error when I run the pgadmin4

When a check my file manager using df -h it tells me that 0 bytes are free out of 9.8 GB of space (using 9.3). This makes no sense and was not the case just a few short hours ago.

I don't understand why pgadmin just randomly crapped out on me, but now I cant run anything I cant even run the autograder or anything at all on my system, all connections are being rejected.

do you know any way to fix this? I can't find anything online, I'm thinking it is something to do with temp files being permanently stored by mistake? I dont know anything about linux so I really am not sure how to fix this

thanks,

Ben Seguin

Seems like when the queries are being ran some of the values in the database are being duplicated. When looking at the outputs in the csv files for each query and comparing it to the tables produced when I run the queries in pgadmin4 on my own test database (which I set up with the same dbexport.sql dump file) the values are multiplied by the query number. For example, the values in Q_2.csv are twice the value compared to the output in pgadmin4 on my test database. The values in Q_3.csv are three times the value compared to the output in my test databse. The values in Q_4.csv are four times the value compared to my test database. I assume that this might be an error somewhere in the queries.py file so I wanted to mention it.

Line 45 of queries.py forwards cursor and conn to drop_database. However, the cursor that's passed in is the same one that's initialized in the top level __name__ == "__main__". Its corresponding conn is closed on line 56 of queries.py. This leads to any follow-up usages of cursor (i.e., on the next attempt to drop & create the database) to fail with the error 'the connection is closed'.

I can PR a fixed copy if you'd like but it should be as simple as localizing the connection and cursor to the function where it's used.

Hi Gabriel,

I've been stuck on the following error for a little bit and had a question. After running queries.py, I get the following:

This error message is vague I was wondering if it were acceptable to add a line of code to print the exception in the except block in get_time(). This is only so I can debug why I can't get the autograder to work, and wanted to ask before making any changes because of the Academic Integrity policy. If not, it would be great if you had an idea what would be causing this, however I'm assuming it is something within my code.

Thanks,
Steven

Hi there,
I have been attempting to push to main via the Ubuntu remote using "git push -u origin main" as instructed but nothing happens. The "git status" command returns everything correctly and I know the key token I'm using is right because I can pull just fine. However, "git push -u origin main" just returns nothing, then immediately prompts me for my user/password again in an infinite loop. Any ideas?

Thanks,
Joey V.

I met a same trouble with an other student who sent a thread in Project 1 Q&A. Professor told him need submit an issue request to here. But he didn't post his issues, so I will pass on what he said:
"
Hello, I have a few concerns.

1.My queries.py, when ran contains the wrong password so there's an incorrect password error when I run the script, as it is set to 1234, however this works when I change the password to postgres.

2.When I use the following command as shown in the spec: C:\Program Files\PostgreSQL\13\bin>pg_dump.exe --file "C:\Users\user\
Desktop\db_dump.sql" --host "localhost" --port "5433" --username "postgres" --verbose
--format=p "postgres", It doesen't work, but if I change the port to 5432 it works but the db_dump.sql only contains header information and not the database schema information. However, this command worked for me:
pg_dump.exe --file "C:\Users\sheri\Documents\db_dump.sql" --host "localhost" --port "5432" --username "postgres" --verbose --format=p "project_database"

3.When I've made these changes and I run queries.py it says internal or external command psql not found, and I fixed this by adding psql to my environment variables on my machine, but it still doesen't work and gives that error even though I can run psql anywhere on my machine.

4.Even though I shouldn't modify queries.py I modified this line so psql command could work so I could test it:
psql_path = r'C:\Program Files\PostgreSQL\16\bin\psql.exe'
command = f'"{psql_path}" -h {host} -U {user} -d "query_database" -a -f "{os.path.join(dir_path, "dbexport.sql")}"'
print(command)
env = {'PGPASSWORD': password}
subprocess.run(command, shell=True, check=True, env=env)
And when I run psql works now however I run into this error:

Some clarification to these issues or regarding the queries.py and the autograder would be much appreciated.
"

Hi,
I posted this question in the Q&A but read that I should ask queries.py relation questions here.
Basically, I was able to insert data into the project_database we created using psycopg2

However, I am now trying to run the queries.py file in a virtual Linux environment to test my queries and receiving the following error:

connection failure: Connection refused Is the server running on that host and accepting TCP/IP connections?

I have not changed any of the code except for entering the queries in their respective locations, and the name of our database is project_database. Also, I have not been able to change the password for the user 'postgres' to '1234' so i have tried changing the db_password value to my password for testing purposes.

Hi there,
I have been consistently running into problem after problem attempting to run queries.py, and now I am completely stuck.

Here is what I have done so far:

1. Made a psycopg3 script that creates a db "project_database" of my own design, then populates it with the data from the JSON files.
2. Found out that "queries.py" doesn't work in Windows. Entered a Ubuntu environment from one of my class' VMs, installed postgreSQL & pgAdmin.
3. Ran my script and confirmed that the database is correct and populated using pgAdmin.
4. Exported the db to a .sql file using "pg_dump -U postgres -h localhost project_database >> dbexport.sql" (inside Ubuntu to get UTF-8 encoding, which was a whole issue in itself). The result is a whopping 180mb in size, causing more problems related to my VM's allocated disk space as well as GitHub's maximum single file size.
5. Found a way to run "queries.py" inside a shared folder.

Finally, queries.py actually runs, but now it is riddled with errors. I can see it going over all of my tables and all of the data using the correct names, but I can see a bunch of error prints along the way. I have attached an image of the end of the print.

I apologize for the harsh tone, but I have spent more time just trying to understand what is expected from me and getting the autograder to work than actually doing the project, which I think is a major problem. Please let me know what I can do so I can finally get started on queries and help my groupmates understand how to start.

Thanks for your time,
Joey V.